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); } }