public void TestSelectedSheet_bug44523()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
ConfirmActiveSelected(sheet1, true);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetSelectedTab(1);
// Demonstrate bug 44525:
// Well... not quite, since isActive + isSelected were also Added in the same bug fix
if (sheet1.IsSelected)
{
throw new AssertionException("Identified bug 44523 a");
}
wb.SetActiveSheet(1);
if (sheet1.IsActive)
{
throw new AssertionException("Identified bug 44523 b");
}
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, true);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
}
public void SplitSheetsXls(string fileName, string excelPrefixName)
{
HSSFWorkbook workbook = null;
using (FileStream stream = File.OpenRead(fileName))
{
workbook = new HSSFWorkbook(stream);
}
for (int i = 0; i < workbook.Count; i++)
{
HSSFSheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
HSSFWorkbook copy = new HSSFWorkbook();
sheet.CopyTo(copy, sheet.SheetName, true, true);
copy.SetActiveSheet(0);
string name = excelPrefixName + sheet.SheetName + ".xlsx";
using (FileStream fs = new FileStream(name, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
copy.Write(fs);
fs.Close();
}
}
}
/// <summary>
/// WriterExcel
/// </summary>
/// <param name="hssfworkbookDown"></param>
/// <param name="sheetIndex"></param>
/// <param name="DT"></param>
public static void WriterExcelP(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT)
{
try
{
#region 设置单元格样式
//字体
HSSFFont fontS9 = FontS9(hssfworkbookDown);
//表格
ICellStyle tableS1 = TableS1(hssfworkbookDown);
tableS1.SetFont(fontS9);
ICellStyle tableS2 = TableS2(hssfworkbookDown);
tableS2.SetFont(fontS9);
ICellStyle tableS3 = TableS3(hssfworkbookDown);
tableS3.SetFont(fontS9);
ICellStyle tableS4 = TableS4(hssfworkbookDown);
tableS4.SetFont(fontS9);
#endregion
HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex);
hssfworkbookDown.SetSheetHidden(sheetIndex, false);
hssfworkbookDown.SetActiveSheet(sheetIndex);
int n = 1;//因为模板有表头，所以从第2行开始写
for (int j = 0; j < DT.Rows.Count; j++)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n);
dataRow.CreateCell(0);
dataRow.Cells[0].SetCellValue(j + 1);
for (int i = 1; i < project_excel.Length; i++)
{
dataRow.CreateCell(i);
dataRow.Cells[i].SetCellValue(DT.Rows[j][project_excel[i]].ToString());
dataRow.Cells[i].CellStyle = tableS2;
if (project_excel[i].ToString() == "content")
{
dataRow.Cells[i].CellStyle = tableS3;
}
}
dataRow.CreateCell(project_excel.Length);
dataRow.Cells[project_excel.Length].SetCellValue(DT.Rows[j]["progress"].ToString() == "100" ? "已完成" : "");
if (DT.Rows[j]["progress"].ToString() == "100")
{
for (int i = 0; i <= project_excel.Length; i++)
{
dataRow.Cells[i].CellStyle = tableS1;
if (i != project_excel.Length && project_excel[i].ToString() == "content")
{
dataRow.Cells[i].CellStyle = tableS4;
}
}
}
}
}
catch (Exception ex)
{
return;
}
}
public async Task Generate(int fromYear, int toYear)
{
if (String.IsNullOrEmpty(sourceExcelFile))
{
throw new ArgumentException("File template tidak tersedia");
}
if (String.IsNullOrEmpty(destExcelFile))
{
throw new ArgumentNullException("File tujuan belum diberikan");
}
// Make a copy of template for pushing out the resul
File.Copy(sourceExcelFile, destExcelFile, true);
HSSFWorkbook workbook = null;
using (FileStream fileStream = new FileStream(destExcelFile, FileMode.Open, FileAccess.ReadWrite))
{
workbook = new HSSFWorkbook(fileStream);
workbook.ForceFormulaRecalculation = true;
}
ProcessSheet2(workbook, fromYear, toYear);
// Prepare members information
members.Clear();
stateHistories.Clear();
members = await RetrieveProcessedMembers();
stateHistories = GetMemberStateHistories(members);
ProcessSheet3(workbook, fromYear, toYear);
ISheet sheet4 = workbook.GetSheetAt(4);
sheet4.ForceFormulaRecalculation = true;
ISheet sheet5 = workbook.GetSheetAt(5);
ProcessSheet5(workbook, fromYear, toYear);
sheet5.ForceFormulaRecalculation = true;
ForceWorkbookRecalculation(workbook);
using (FileStream fileStream = new FileStream(destExcelFile, FileMode.Open, FileAccess.ReadWrite))
{
workbook.ForceFormulaRecalculation = true;
workbook.SetActiveSheet(2);
workbook.Write(fileStream);
fileStream.Flush();
}
DateTime now = DateTime.Now;
File.SetLastWriteTime(destExcelFile, now);
File.SetLastAccessTime(destExcelFile, now);
File.SetCreationTime(destExcelFile, now);
members = null;
stateHistories = null;
}
public void SetSheetHidden(string sheetName)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
if (i > -1)
{
_workbook.SetSheetHidden(i, true);
}
if (i < _workbook.NumberOfSheets - 1)
{
_workbook.SetActiveSheet(i + 1);
}
else
{
_workbook.SetActiveSheet(i - 1);
}
}
public new void TestSheetSelection()
{
HSSFWorkbook b = new HSSFWorkbook();
b.CreateSheet("Sheet One");
b.CreateSheet("Sheet Two");
b.SetActiveSheet(1);
b.SetSelectedTab(1);
b.FirstVisibleTab = (1);
Assert.AreEqual(1, b.ActiveSheetIndex);
Assert.AreEqual(1, b.FirstVisibleTab);
}
public NPOIExcelHelper(string templatePath)
{
try
{
stream = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
workbook = new HSSFWorkbook(stream);
workbook.SetActiveSheet(0);
CurrentSheet = workbook.GetSheetAt(0);
}
catch (Exception ex)
{
throw new Exception("NPOI实例化错误:" + ex.ToString());
}
}
/// <summary>
/// Writes the contents of a list of data table to a spreadsheet. A new sheet is used for each table.
/// </summary>
/// <param name="table">The list of DataTables containing data.</param>
/// <param name="outputStream">The stream to write the excel file to</param>
public void WriteToStream(IList <DataTable> tables, Stream outputStream)
{
HSSFWorkbook workbook = new HSSFWorkbook();
if (outputStream == null) // Escape if we have nothing to write to. . .
{
return;
}
if (tables == null || tables.Count == 0)
{
workbook.CreateSheet(); // Create an empty to prevent errors when opening the spreadsheet, even though there's no data to be included. . .
}
for (int i = 0; i < tables.Count; i++) // Iterate through the list, adding a new sheet for each table. . .
{
var table = tables[i];
var columns = new List <string>();
foreach (DataColumn column in table.Columns) // Grab a list of the columns. . .
{
columns.Add(column.Caption);
}
var sheet = workbook.CreateSheet(); // Create the sheet. . .
var columnRow = sheet.CreateRow(0);
for (int j = 0; j < columns.Count; j++) // Write the columns. . .
{
var cell = columnRow.CreateCell(j);
cell.SetCellValue(columns[j]); // Set the values. . .
}
for (int j = 1; j < table.Rows.Count + 1; j++) // Write the rows. . .
{
var row = sheet.CreateRow(j); // Create the row. . .
for (int k = 0; k < columns.Count; k++)
{
var cell = row.CreateCell(k);
cell.SetCellValue((string)table.Rows[j - 1][k]); // Set the values. . .
}
}
}
workbook.SetActiveSheet(0); // Make sure that the first item is selected. . .
workbook.Write(outputStream); // Write the spreadsheet. . .
}
public NPOIExcelHelper(Stream excelStream)
{
stream = excelStream;
try
{
workbook = new HSSFWorkbook(stream);
workbook.SetActiveSheet(0);
CurrentSheet = workbook.GetSheetAt(0);
}
catch (Exception ex)
{
if (stream != null)
{
stream.Close();
}
throw new Exception("NPOI实例化错误:" + ex);
}
}
public void TestSelectedSheetshort()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet("Sheet2");
HSSFSheet sheet3 = (HSSFSheet)wb.CreateSheet("Sheet3");
HSSFSheet sheet4 = (HSSFSheet)wb.CreateSheet("Sheet4");
ConfirmActiveSelected(sheet1, true);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetSelectedTab((short)1);
// see Javadoc, in this case selected means "active"
Assert.AreEqual(wb.ActiveSheetIndex, (short)wb.ActiveSheetIndex);
// Demonstrate bug 44525:
// Well... not quite, since isActive + isSelected were also Added in the same bug fix
if (sheet1.IsSelected)
{
//throw new AssertionFailedError("Identified bug 44523 a");
Assert.Fail("Identified bug 44523 a");
}
wb.SetActiveSheet(1);
if (sheet1.IsActive)
{
//throw new AssertionFailedError("Identified bug 44523 b");
Assert.Fail("Identified bug 44523 b");
}
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, true);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
Assert.AreEqual(0, wb.FirstVisibleTab);
//wb.DisplayedTab=((short)2);
//Assert.AreEqual(2, wb.FirstVisibleTab);
//Assert.AreEqual(2, wb.DisplayedTab);
}
public void TestPlainValueCache()
{
HSSFWorkbook wb = new HSSFWorkbook();
int numberOfSheets = 4098; // Bug 51448 reported that Evaluation Cache got messed up After 256 sheets
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = "SUM(A2:A" + (numberOfSheets + 2) + ")";
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = "new" + i + "!A1";
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
Assert.AreEqual(8394753.0, summaryCell.NumericCellValue);
}
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets)
{
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")");
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = ("new" + i + "!A1");
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
}
static void Main(string[] args)
{
InitializeWorkbook();
//use HSSFCell.SetAsActiveCell() to select B6 as the active column
ISheet sheet1 = hssfworkbook.CreateSheet("ISheet A");
CreateCellArray(sheet1);
sheet1.GetRow(5).GetCell(1).SetAsActiveCell();
//set TopRow and LeftCol to make B6 the first cell in the visible area
sheet1.TopRow = 5;
sheet1.LeftCol = 1;
//use ISheet.SetActiveCell(), the sheet can be empty
ISheet sheet2 = hssfworkbook.CreateSheet("ISheet B");
sheet2.ActiveCell = new CellAddress(1, 5);
//use ISheet.SetActiveCellRange to select a cell range
ISheet sheet3 = hssfworkbook.CreateSheet("ISheet C");
CreateCellArray(sheet3);
sheet3.SetActiveCellRange(2, 20, 1, 50);
//set the ISheet C as the active sheet
hssfworkbook.SetActiveSheet(2);
//use ISheet.SetActiveCellRange to select multiple cell ranges
ISheet sheet4 = hssfworkbook.CreateSheet("ISheet D");
CreateCellArray(sheet4);
List <CellRangeAddress8Bit> cellranges = new List <CellRangeAddress8Bit>();
cellranges.Add(new CellRangeAddress8Bit(1, 5, 10, 100));
cellranges.Add(new CellRangeAddress8Bit(6, 7, 8, 9));
sheet4.SetActiveCellRange(cellranges, 1, 6, 9);
WriteToFile();
}
public static void PrepareForOutput(HSSFWorkbook book)
{
//删除报表定义sheet
for (int i = book.NumberOfSheets - 1; i >= 0; i--)
{
HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
if (IsSystemSheet(sheet))
{
book.RemoveSheetAt(i);
}
}
HSSFFormulaEvaluator.EvaluateAllFormulaCells(book);
//设置打开时强制计算合计项
//for (int i = 0; i < book.NumberOfSheets; i++)
//{
// HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet;
// sheet.ForceFormulaRecalculation = true;
//}
book.SetActiveSheet(0);
}
public void SelectedSheet_bug44523()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
Npoi.Core.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
Npoi.Core.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
Npoi.Core.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
ConfirmActiveSelected(sheet1, true);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetSelectedTab(1);
// see Javadoc, in this case selected means "active"
Assert.AreEqual(wb.ActiveSheetIndex, (short)wb.ActiveSheetIndex);
// Demonstrate bug 44525:
// Well... not quite, since isActive + isSelected were also Added in the same bug fix
if (sheet1.IsSelected)
{
throw new AssertionException("Identified bug 44523 a");
}
wb.SetActiveSheet(1);
if (sheet1.IsActive)
{
throw new AssertionException("Identified bug 44523 b");
}
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, true);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
}
public void TestActiveSheetAfterDelete_bug40414()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet0 = wb.CreateSheet("Sheet0");
NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
// Confirm default activation/selection
ConfirmActiveSelected(sheet0, true);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetActiveSheet(3);
wb.SetSelectedTab(3);
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, true);
ConfirmActiveSelected(sheet4, false);
wb.RemoveSheetAt(3);
// after removing the only active/selected sheet, another should be active/selected in its place
if (!sheet4.IsSelected)
{
throw new AssertionException("identified bug 40414 a");
}
if (!sheet4.IsActive)
{
throw new AssertionException("identified bug 40414 b");
}
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet4, true);
sheet3 = sheet4; // re-align local vars in this Test case
// Some more cases of removing sheets
// Starting with a multiple selection, and different active sheet
wb.SetSelectedTabs(new int[] { 1, 3, });
wb.SetActiveSheet(2);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
ConfirmActiveSelected(sheet3, false, true);
// removing a sheet that is not active, and not the only selected sheet
wb.RemoveSheetAt(3);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
// removing the only selected sheet
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet2, true, true);
// The last remaining sheet should always be active+selected
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, true, true);
}
/// <summary>
/// DataTable 数据添加到Excel模板中.
/// </summary>
/// <param name="newFilePath"></param>
/// <param name="dt">输入DataTable</param>
/// <param name="startRow">NPOI开始的行号,从0开始.(对应的Excel列从0开始行).</param>
/// <param name="rowHeight">行高.(Excel中设置的值,方法中已进行x20计算)</param>
/// <param name="styleCell_row">样式单元的行号,默认0行.</param>
/// <param name="styleCell_col">样式单元的列号,默认0列.</param>
/// <returns></returns>
public static string CreateExcelTemplate(string newFilePath, DataSet ds, string sheetName = "sheet1")
{
try
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
//单元格的四周边框设置为细边框.
ICellStyle style = workbook.CreateCellStyle();//.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//cell.CellStyle = style;
int startRowNum = 0;
foreach (DataTable dt in ds.Tables)
{
//if (dt.Rows[0][1] + "" == dt.Rows[0][2] + "")
//{
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + dt.Rows.Count - 1, 1, 2));
//}
//else
//{
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + dt.Rows.Count - 1, 1, 1));
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + dt.Rows.Count - 1, 2, 2));
//}
//DataRow preRow = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow curRow = dt.Rows[i];
//preRow = i == 0 ? dt.Rows[0] : dt.Rows[i - 1];
//if(curRow[1]+""==preRow[1]+""&& curRow[2] + ""==preRow[2] + "" && curRow[1] + ""==curRow[1] + "")
//{
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i-1<0?0:i, i, 1, 2));
//}else
//{
// if (curRow[0] + ""==preRow[0] + "")
// {
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 1, 1));
// }
// if (curRow[1] + ""==preRow[1] + "")
// {
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 2, 2));
// }
//}
//DataRow dr = dt.Rows[i];
IRow excelRow = sheet.CreateRow(startRowNum + i);
//插入行.第"startRow"到第"sheet.LastRowNum"行移动"dt.Rows.Count"行,bool copyRowHeight, bool resetOriginalRowHeight
//sheet.ShiftRows(i, i + 1, dt.Rows.Count, true, true);
for (int j = 0; j < dt.Columns.Count; j++)
{
var excelCell = excelRow.CreateCell(j, CellType.String);
#region 单元格样式
excelCell.CellStyle = style;// sheet.GetRow(styleCell_row).Cells[styleCell_col].CellStyle;
#endregion
excelCell.SetCellValue(curRow[j].ToString());
}
}
startRowNum += dt.Rows.Count;
}
Dictionary <string, List <int> > dic = new Dictionary <string, List <int> >();
List <int[]> List = new List <int[]>();
int startRowNo = 0, endRowNo = 0, startColNo, endColNo;
ICell preCel, curCel;
//合并相同的单元格
for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);
string cel1Val = row.GetCell(1).StringCellValue;
string cel2Val = row.GetCell(2).StringCellValue;
string precel1Val = sheet.GetRow(rowIndex - 1).GetCell(1).StringCellValue;
string precel2Val = sheet.GetRow(rowIndex - 1).GetCell(2).StringCellValue;
if (!dic.ContainsKey(cel1Val))
{
dic.Add(cel1Val, new List <int>());
}
if (!dic.ContainsKey(cel2Val))
{
dic.Add(cel2Val, new List <int>());
}
if (precel1Val == cel1Val && precel1Val == cel2Val)
{
if (dic[precel1Val].Count > 0)
{
dic[precel1Val][1] = rowIndex;
}
endRowNo = rowIndex;
}
else
{
List.Add(new int[] { startRowNo, endRowNo });
}
if (cel1Val == cel2Val)
{
startColNo = 1;
endColNo = 2;
}
//if (cellVal + "" == cel2 + "" && curRow[2] + "" == preRow[2] + "" && curRow[1] + "" == curRow[1] + "")
//{
// //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1 < 0 ? 0 : i, i, 1, 2));
//}
//else
//{
// //if (curRow[0] + "" == preRow[0] + "")
// //{
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 1, 1));
// //}
// //if (curRow[1] + "" == preRow[1] + "")
// //{
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 2, 2));
// //}
//}
//if (cell == null)
//{
// continue;
//}
//int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
//columnWidth = columnWidth < contextLength ? contextLength : columnWidth;
}
//自适应列宽
AutoColumnWidth(sheet, 8);
//保存Excel
workbook.SetSheetOrder(sheet.SheetName, 0);//设置表格位置
workbook.SetActiveSheet(0);
using (FileStream fs = new FileStream(newFilePath, FileMode.Create))
{
workbook.Write(fs);
}
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
