public void TestBug50298() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls"); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); ISheet sheet = wb.CloneSheet(0); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)"); wb.SetSheetName(wb.GetSheetIndex(sheet), "copy"); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy"); wb.SetSheetOrder("copy", 0); assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received"); wb.RemoveSheetAt(0); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); // check that the overall workbook serializes with its correct size int expected = wb.Workbook.Size; int written = wb.Workbook.Serialize(0, new byte[expected * 2]); Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected); HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb); assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); }
public void SetSheetName(string sheetName, string newSheetName, ref int sheetIndex) { int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName)); _currentSheet = _workbook.GetSheetAt(i); _workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName); _workbook.SetSheetOrder(newSheetName, _workbook.NumberOfSheets); _defaultExcelCellStyle = new ExcelCellStyle(); }
public void createManagerTab(List <ClientTradeSummary> tradeStatistics_, List <string> tradingDays_, HSSFWorkbook wb_, string tabName_) { ISheet tb = wb_.CreateSheet(tabName_); wb_.SetSheetOrder(tabName_, 0); sortAlgos(); generateDateLine(tradingDays_, tb, wb_); addStrategyLine(tb, wb_); addColumeNameLine(tb, wb_); addStatistics(tradeStatistics_, tb, wb_); computeAggregateStatistics(tradeStatistics_); addSummaryLine(tb, wb_); setAutoSizeColumn(tb, columeNb); mergeCells(tb); currentLine = 0; }
public void TestSetSheetOrderHSSF() { IWorkbook wb = new HSSFWorkbook(); ISheet s1 = wb.CreateSheet("first sheet"); ISheet s2 = wb.CreateSheet("other sheet"); IName name1 = wb.CreateName(); name1.NameName = (/*setter*/ "name1"); name1.RefersToFormula = (/*setter*/ "'first sheet'!D1"); IName name2 = wb.CreateName(); name2.NameName = (/*setter*/ "name2"); name2.RefersToFormula = (/*setter*/ "'other sheet'!C1"); IRow s1r1 = s1.CreateRow(2); ICell c1 = s1r1.CreateCell(3); c1.SetCellValue(30); ICell c2 = s1r1.CreateCell(2); c2.CellFormula = (/*setter*/ "SUM('other sheet'!C1,'first sheet'!C1)"); IRow s2r1 = s2.CreateRow(0); ICell c3 = s2r1.CreateCell(1); c3.CellFormula = (/*setter*/ "'first sheet'!D3"); ICell c4 = s2r1.CreateCell(2); c4.CellFormula = (/*setter*/ "'other sheet'!D3"); // conditional formatting ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1"); IConditionalFormattingRule[] cfRules = { rule1 }; CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5 }; sheetCF.AddConditionalFormatting(regions, cfRules); wb.SetSheetOrder("other sheet", 0); // names Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula); Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula); // cells Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula); Assert.AreEqual("'first sheet'!D3", c3.CellFormula); Assert.AreEqual("'other sheet'!D3", c4.CellFormula); // conditional formatting IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1); Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2); }
/// <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); } }
//刷新表注释 private void RefreshNote(string fileFullName) { bool change = false; for (int i = 0; i < mFields.Count; ++i) { var filed = mFields[i]; if (filed.Enum) { change = true; break; } } if (!change) { return; } IWorkbook workbook = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite)); ISheet sheet = workbook.GetSheetAt(0); string sheetName = sheet.SheetName; ISheet sheetBack = workbook.CreateSheet("__sheetBackup"); sheetBack.CreateFreezePane(2, START_ROW); int columnNum = 0; for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.LastCellNum > columnNum) { columnNum = row.LastCellNum; } IRow rowBack = sheetBack.CreateRow(i); for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j) { string val = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK)); var cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); Util.SetCellString(cell, val); } } for (int i = 0; i < columnNum; ++i) { sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i)); } for (int i = 0; i < mFields.Count; ++i) { var filed = mFields[i]; if (filed.Enum) { IDataValidationHelper helper = sheet.GetDataValidationHelper(); CellRangeAddressList cellRange = new CellRangeAddressList(START_ROW, 65535, i, i); IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(GetEnumList(filed.Type)); IDataValidation dataValidation = helper.CreateValidation(constraint, cellRange); dataValidation.SuppressDropDownArrow = false; dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type)); sheetBack.AddValidationData(dataValidation); } } workbook.RemoveSheetAt(0); workbook.SetSheetOrder(sheetBack.SheetName, 0); workbook.SetSheetName(0, sheetName); using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) { workbook.Write(stream); } }