public void Test3DArea() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet1 = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); wb.CreateSheet(); wb.SetSheetName(1, "Sheet2"); IRow row = sheet1.CreateRow(0); ICell cell = row.CreateCell(0); cell.CellFormula = ("isblank(Sheet2!A1:A1)"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); CellValue result = fe.Evaluate(cell); Assert.AreEqual(CellType.Boolean, result.CellType); Assert.AreEqual(true, result.BooleanValue); cell.CellFormula = ("isblank(D7:D7)"); result = fe.Evaluate(cell); Assert.AreEqual(CellType.Boolean, result.CellType); Assert.AreEqual(true, result.BooleanValue); }
public void Test3DArea() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); wb.CreateSheet(); wb.SetSheetName(1, "Sheet2"); Row row = sheet1.CreateRow(0); Cell cell = row.CreateCell((short)0); cell.CellFormula = ("isblank(Sheet2!A1:A1)"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb); //fe.SetCurrentRow(row); NPOI.SS.UserModel.CellValue result = fe.Evaluate(cell); Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType); Assert.AreEqual(true, result.BooleanValue); cell.CellFormula = ("isblank(D7:D7)"); result = fe.Evaluate(cell); Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType); Assert.AreEqual(true, result.BooleanValue); }
/// <summary> /// 增加一個新的表格,並將新增的表格作為活動表 /// </summary> /// <param name="sheetName">表名稱</param> public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex) { int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName)); _currentSheet = _workbook.CloneSheet(i); sheetIndex = _workbook.GetSheetIndex(_currentSheet); _workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName); _defaultExcelCellStyle = new ExcelCellStyle(); }
/// <summary> /// DataTable 数据至二进制 /// </summary> /// <param name="dt">数据表</param> /// <param name="header"></param> /// <param name="commnet"></param> /// <returns>二进制数组</returns> public byte[] Export(DataTable dt) { _workBook = new HSSFWorkbook(); ISheet sheet = _workBook.CreateSheet(); if (header == "钢联数据") { _workBook.SetSheetName(0, "Sheet1"); } else { _workBook.SetSheetName(0, "数据"); } //设置列宽 setColumnWidth(dt, sheet); int rowIndex = 0; foreach (DataRow dr in dt.Rows) { //新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = _workBook.CreateSheet(); } if (!string.IsNullOrEmpty(header)) { IRow titleRow = sheet.CreateRow(0); createTitleComment(sheet, titleRow); rowIndex = 1; } } //设置单元格数据 setCellsValue(sheet, dt, dr, rowIndex); rowIndex++; } using (MemoryStream ms = new MemoryStream()) { _workBook.Write(ms); ms.Flush(); ms.Position = 0; _workBook.Close(); _workBook = null; return(ms.ToArray()); } }
public void TestToFormulaString() { Ref3DPtg target = new Ref3DPtg("A1", (short)0); HSSFWorkbook wb = CreateWorkbookWithSheet("my sheet"); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1", target.ToFormulaString(book)); wb.SetSheetName(0, "ProfitAndLoss"); Assert.AreEqual("ProfitAndLoss!A1", target.ToFormulaString(book)); wb.SetSheetName(0, "profit+loss"); Assert.AreEqual("'profit+loss'!A1", target.ToFormulaString(book)); }
/// <summary> /// 导出订单汇总 /// </summary> public static void Excel_DingdanHZ(String yearMonth, DataTable dt, string openFilePath, String saveFilePath) { try { FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); hssfworkbook.SetSheetName(0, yearMonth); ISheet sheet1 = hssfworkbook.GetSheet(yearMonth); InsertRows2(sheet1, 1, dt.Rows.Count); //写入数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet1.GetRow(1 + i); row.GetCell(0).SetCellValue(dt.Rows[i]["AgentId"].ToString()); row.GetCell(1).SetCellValue(dt.Rows[i]["AgentName"].ToString()); row.GetCell(2).SetCellValue(dt.Rows[i]["CareerStatus"].ToString()); row.GetCell(3).SetCellValue(dt.Rows[i]["Rank"].ToString()); var state = dt.Rows[i]["State"].ToString(); row.GetCell(4).SetCellValue(state == "0" ? "已删除" : (state == "1" ? "正常" : (state == "-1" ? "新添加" : ""))); row.GetCell(5).SetCellValue(dt.Rows[i]["CountOrders"].ToString()); row.GetCell(6).SetCellValue(dt.Rows[i]["Price"].ToString()); } sheet1.ForceFormulaRecalculation = true; file = new FileStream(saveFilePath, FileMode.Create); hssfworkbook.Write(file); file.Close(); } catch (Exception ex) { throw; } }
/// <summary> /// 导出产品汇总 /// </summary> public static void Excel_ChanpinHZ(String yearMonth, List <OrdersDetail> list, string openFilePath, String saveFilePath) { try { FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); hssfworkbook.SetSheetName(0, yearMonth); ISheet sheet1 = hssfworkbook.GetSheet(yearMonth); InsertRows2(sheet1, 1, list.Count); //写入数据 for (int i = 0; i < list.Count; i++) { IRow row = sheet1.GetRow(1 + i); row.GetCell(0).SetCellValue(list[i].ProductName); row.GetCell(1).SetCellValue((double)list[i].UnitPrice); row.GetCell(2).SetCellValue(list[i].Num); row.GetCell(3).SetCellValue((double)list[i].Price); } sheet1.ForceFormulaRecalculation = true; file = new FileStream(saveFilePath, FileMode.Create); hssfworkbook.Write(file); file.Close(); } catch (Exception ex) { throw; } }
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 TestEvaluateBooleanInCell_bug44508() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); cell.CellFormula = ("1=1"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); try { fe.EvaluateInCell(cell); } catch (FormatException) { Assert.Fail("Identified bug 44508"); } Assert.AreEqual(true, cell.BooleanCellValue); wb.Close(); }
public void TestToFormulaString() { Area3DPtg target = new Area3DPtg("A1:B1", (short)0); string sheetName = "my sheet"; HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "Sheet1"); Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "C64"); Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book)); }
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell((short)0); cell.CellFormula = ("SUM(A32769:A32770)"); // put some values in the cells to make the evaluation more interesting sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31); sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11); //HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); NPOI.SS.UserModel.CellValue result; try { result = fe.Evaluate(cell); } catch (Exception e) { if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!")) { Assert.Fail("Identifed bug 44539"); } throw; } Assert.AreEqual(NPOI.SS.UserModel.CellType.Numeric, result.CellType); Assert.AreEqual(42.0, result.NumberValue, 0.0); }
public static void SaveSOExcel(List <So> soList, List <List <SoItemsContentAndState> > soitemsListList) { if (soList.Count() != soitemsListList.Count()) { MessageBox.Show("Internal Error. Please send the log file to the Author"); Logger.Error(soList.Count() + "," + soitemsListList.Count()); return; } FileStream file; try { file = new FileStream(@"SoTemplate.dll", FileMode.Open, FileAccess.Read); } catch (Exception) { MessageBox.Show("Please check the SoTemplate.dll."); return; } hssfworkbook = new HSSFWorkbook(file); WriteDsiInfo(); for (int i = 0; i < soList.Count(); i++) { ISheet sheet = hssfworkbook.CloneSheet(0); FillTheSoSheet(sheet, soList[i], soitemsListList[i]); hssfworkbook.SetSheetName(3 + i, "SO" + i.ToString()); } hssfworkbook.RemoveSheetAt(0); hssfworkbook.RemoveSheetAt(0); hssfworkbook.RemoveSheetAt(0); WriteToFile(); }
public void TestParseStringLiterals_bug28754() { StringPtg sp; try { sp = (StringPtg)ParseSingleToken("\"test\"\"ing\"", typeof(StringPtg)); } catch (Exception e) { if (e.Message.StartsWith("Cannot Parse")) { throw new AssertFailedException("Identified bug 28754a"); } throw e; } Assert.AreEqual("test\"ing", sp.Value); HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell((short)0); cell.CellFormula = ("right(\"test\"\"ing\", 3)"); String actualCellFormula = cell.CellFormula; if ("RIGHT(\"test\"ing\",3)".Equals(actualCellFormula)) { throw new AssertFailedException("Identified bug 28754b"); } Assert.AreEqual("RIGHT(\"test\"\"ing\",3)", actualCellFormula); }
public void TestDuplicateNames() { HSSFWorkbook b = new HSSFWorkbook(); b.CreateSheet("Sheet1"); b.CreateSheet(); b.CreateSheet("name1"); try { b.CreateSheet("name1"); Assert.Fail(); } catch (ArgumentException)// pass { } b.CreateSheet(); try { b.SetSheetName(3, "name1"); Assert.Fail(); } catch (ArgumentException)// pass { } try { b.SetSheetName(3, "name1"); Assert.Fail(); } catch (ArgumentException)// pass { } b.SetSheetName(3, "name2"); b.SetSheetName(3, "name2"); b.SetSheetName(3, "name2"); HSSFWorkbook c = new HSSFWorkbook(); c.CreateSheet("Sheet1"); c.CreateSheet("Sheet2"); c.CreateSheet("Sheet3"); c.CreateSheet("Sheet4"); }
public void TestMultiNamedRange() { // Create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // Create a worksheet 'sheet1' in the new workbook wb.CreateSheet(); wb.SetSheetName(0, "sheet1"); // Create another worksheet 'sheet2' in the new workbook wb.CreateSheet(); wb.SetSheetName(1, "sheet2"); // Create a new named range for worksheet 'sheet1' NPOI.SS.UserModel.Name namedRange1 = wb.CreateName(); // Set the name for the named range for worksheet 'sheet1' namedRange1.NameName = ("RangeTest1"); // Set the reference for the named range for worksheet 'sheet1' namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41"); // Create a new named range for worksheet 'sheet2' NPOI.SS.UserModel.Name namedRange2 = wb.CreateName(); // Set the name for the named range for worksheet 'sheet2' namedRange2.NameName = ("RangeTest2"); // Set the reference for the named range for worksheet 'sheet2' namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21"); // Write the workbook to a file // Read the Excel file and verify its content wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1")); Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName); Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula); NPOI.SS.UserModel.Name nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2")); Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName); Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula); }
public void ChangeSheetName(int k, string sheetname) { k = k - 1; if (wb == null) { return; } wb.SetSheetName(k, sheetname); //wb.CreateSheet(sheetname);//克隆原有sheet页作为基础 //wb.SetSheetOrder(sheetname, k);//将最新的sheet页挪到第一页 }
public void TestSetFormulaWithRowBeyond32768_Bug44539() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell((short)0); cell.CellFormula = ("SUM(A32769:A32770)"); if ("SUM(A-32767:A-32766)".Equals(cell.CellFormula)) { Assert.Fail("Identified bug 44539"); } Assert.AreEqual("SUM(A32769:A32770)", cell.CellFormula); }
/// <summary> /// 直接导出Datatable数据到Excel /// </summary> /// <param name="exportTable">导出的Table信息</param> /// <param name="title">Sheet名称</param> /// <returns></returns> public static HSSFWorkbook ExportFromTable(DataTable exportTable, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); workbook.SetSheetName(0, title); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = Company; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = Author; si.ApplicationName = ApplicationName; si.Title = title; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < exportTable.Columns.Count; i++) { ICell headerCell = headerRow.CreateCell(i); headerCell.SetCellValue(exportTable.Columns[i].ColumnName?.ToString()); } for (int i = 0; i < exportTable.Rows.Count; i++) { IRow dataRow = sheet.CreateRow(i + 1); for (int j = 0; j < exportTable.Columns.Count; j++) { ICell dataCell = dataRow.CreateCell(j); dataCell.SetCellValue(exportTable.Rows[i][j]?.ToString()); } } for (int i = 0; i < exportTable.Columns.Count; i++) { sheet.AutoSizeColumn(i); } return(workbook); }
public void TestBug57231() { HSSFWorkbook wb = HSSFTestDataSamples .OpenSampleWorkbook("57231_MixedGasReport.xls"); HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet; HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet; int idx = wb.GetSheetIndex(newSheet); wb.SetSheetName(idx, "newName"); // Write the output to a file HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.IsNotNull(wbBack); Assert.IsNotNull(wbBack.GetSheet("master")); Assert.IsNotNull(wbBack.GetSheet("newName")); }
public static void Excel_daochu(DataTable dt, string filepath, bool isCg)//常规 { try { string wj = ""; if (isCg) { wj = "常规"; } FileStream file = new FileStream(filepath + "导出模板" + wj + ".xls", FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); hssfworkbook.SetSheetName(0, "数据"); ISheet sheet1 = hssfworkbook.GetSheet("数据"); InsertRows(sheet1, 1, dt.Rows.Count, hssfworkbook, dt.Columns.Count); //列标题 for (int i = 0; i < dt.Columns.Count; i++) { sheet1.GetRow(0).GetCell(i).SetCellValue(dt.Columns[i].ColumnName); } //写入数据 for (int j = 1; j <= dt.Rows.Count; j++) { //dt.Columns[0].ColumnName; //IRow row1 = sheet1.GetRow(j); for (int i = 0; i < dt.Columns.Count; i++) { sheet1.GetRow(j).GetCell(i).SetCellValue(dt.Rows[j - 1][i].ToString()); } } sheet1.ForceFormulaRecalculation = true; file = new FileStream(filepath + dao_name(), FileMode.Create); hssfworkbook.Write(file); file.Close(); } catch (Exception) { throw; } }
/// <summary> /// 导出收入明细 /// </summary> public static void Excel_IncomeMX(String yearMonth, List <Income> list, string openFilePath, String saveFilePath) { try { FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); hssfworkbook.SetSheetName(0, yearMonth); ISheet sheet1 = hssfworkbook.GetSheet(yearMonth); InsertRows2(sheet1, 1, list.Count); //写入数据 for (int i = 0; i < list.Count; i++) { IRow row = sheet1.GetRow(1 + i); row.GetCell(0).SetCellValue(list[i].YearMonth); row.GetCell(1).SetCellValue(list[i].AgentId); row.GetCell(2).SetCellValue(list[i].AgentName); row.GetCell(3).SetCellValue(list[i].CareerStatus); row.GetCell(4).SetCellValue(list[i].Rank); row.GetCell(5).SetCellValue((double)list[i].SalesServiceMoney); row.GetCell(6).SetCellValue((double)list[i].PersonalServiceMoney); row.GetCell(7).SetCellValue((double)list[i].MarketServiceMoney); row.GetCell(8).SetCellValue((double)list[i].RegionServiceMoney); row.GetCell(9).SetCellValue((double)list[i].RegionServiceYum); row.GetCell(10).SetCellValue((double)list[i].IncomeMoney); } sheet1.ForceFormulaRecalculation = true; file = new FileStream(saveFilePath, FileMode.Create); hssfworkbook.Write(file); file.Close(); } catch (Exception ex) { throw; } }
public void SalvarDataTable(DataTable dt, string path) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //Criar uma tabela em branco ISheet sheet = hssfworkbook.CreateSheet(); //Colocar dados nela for (int i = 0; i < dt.Rows.Count; i++) { sheet.CreateRow(i); for (int j = 0; j < dt.Columns.Count; j++) { sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } hssfworkbook.CreateSheet(); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), sheet.SheetName); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; //Write the stream data of workbook to the root directory FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); }
/// <summary> /// 打开xls文件,我修改成了,如果没有这个文件,就创建这个文件 /// </summary> /// <param name="fileName">文件名</param> /// <param name="sheetName">工作表名</param> /// <param name="clear">清空标识?</param> /// <returns></returns> public string open_xls(string fileName = "buyma-need-sell.xls", string sheetName = "data", bool clear = true) { try { //文件名 全路径 strFilename = System.IO.Directory.GetCurrentDirectory() + "\\" + fileName; if (System.IO.File.Exists(strFilename) == false) { //这里有修改项,如果没有这个xls文件,我就进行创建操作 //System.IO.File.Create(strFilename); xlBook = new HSSFWorkbook(); xlBook.CreateSheet(sheetName); } else { if (isFileCanWrite(strFilename) == false) { return("当該ファイル(" + fileName + ")は既に他のアプリより開いている状態であるため、まず関連アプリを閉じてください"); } System.IO.FileStream fs = new System.IO.FileStream(strFilename, System.IO.FileMode.Open); xlBook = new HSSFWorkbook(fs); xlBook.SetSheetName(0, sheetName); sheetClear(sheetName); } //在工作簿中以修改第一个工作表为指定工作表名 xlSheet = xlBook.GetSheet(sheetName); working_row = 1; _hasOpen = true; return(""); } catch (Exception ex) { return("sheet [data] is not exit." + ex.ToString()); throw; } }
public void TestInvoke() { HSSFWorkbook wb; NPOI.SS.UserModel.Sheet sheet ; Cell cell; if (false) { // TODO - this code won't work until we can create user-defined functions directly with POI wb = new HSSFWorkbook(); sheet = wb.CreateSheet(); wb.SetSheetName(0, "Sheet1"); NPOI.SS.UserModel.Name hssfName = wb.CreateName(); hssfName.NameName = ("myFunc"); } else { // This sample spreadsheet already has a VB function called 'myFunc' wb = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls"); sheet = wb.GetSheetAt(0); Row row = sheet.CreateRow(0); cell = row.CreateCell(1); } cell.CellFormula = ("myFunc()"); String actualFormula = cell.CellFormula; Assert.AreEqual("myFunc()", actualFormula); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); NPOI.SS.UserModel.CellValue evalResult = fe.Evaluate(cell); // Check the return value from ExternalFunction.evaluate() // TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, evalResult.CellType); Assert.AreEqual(ErrorEval.FUNCTION_NOT_IMPLEMENTED.ErrorCode, evalResult.ErrorValue); }
public void SetSheetName(int index, string sheetName) { workbook.SetSheetName(index, sheetName); }
private void tsbExcel_Click(object sender, EventArgs e) { HSSFWorkbook workbook = null; using (MemoryStream ms = new MemoryStream(Resource.template)) { workbook = new HSSFWorkbook(ms); // 工作簿 } Dictionary <string, HSSFCellStyle> styleDic = new Dictionary <string, HSSFCellStyle>(); Dictionary <string, IFont> fontDic = new Dictionary <string, IFont>(); //准备sheet var sheetIndex = 0; foreach (I3ReportData rd in reportDatas.Datas) { if (sheetIndex > 0) { workbook.CloneSheet(0); } sheetIndex++; } sheetIndex = 0; foreach (I3ReportData rd in reportDatas.Datas) { string sheetName = string.IsNullOrEmpty(rd.Name) ? ("sheet" + (sheetIndex + 1).ToString()) : rd.Name; //ISheet sheet = workbook.CreateSheet(sheetName); ISheet sheet = workbook.GetSheetAt(sheetIndex); workbook.SetSheetName(sheetIndex, sheetName); //设置列宽 var colIndex = -1; foreach (I3ReportCol col in rd.Cols) { colIndex++; if (col.Width == 0) { sheet.SetColumnHidden(colIndex, true); } else { //int width = (int)Math.Ceiling(col.Width * 34.612159); //向上取整,与excel中保持一致 //34.612159是从ebiao导入excel处获取的 //在列旁边按住鼠标不动,可以显示默认单位与像素的对应关系,10单位对应85像素,即1像素对应10/85单位 (好像与excel默认字体有关) //SetColumnWidth的单位是1/256,因此1像素对应10*256/85=30.117647 double dw = (double)col.Width * (double)10 * (double)256 / (double)85; int width = (int)Math.Ceiling(dw); sheet.SetColumnWidth(colIndex, width); } } //导出数据 int rowCount = 0; foreach (I3PrintArea area in rd.PrintAreas.Dic.Values) { exportAreaToSheet(area, workbook, sheet, ref rowCount, styleDic, fontDic); } //页面设置 //列宽会有微调导致在excel中左右分页 sheet.SetMargin(MarginType.RightMargin, rd.PageSetting.PaperRightMarginMM / (float)25.4); sheet.SetMargin(MarginType.TopMargin, rd.PageSetting.PaperTopMarginMM / (float)25.4); sheet.SetMargin(MarginType.LeftMargin, rd.PageSetting.PaperLeftMarginMM / (float)25.4); sheet.SetMargin(MarginType.BottomMargin, rd.PageSetting.PaperBottomMarginMM / (float)25.4); sheet.PrintSetup.Landscape = rd.PageSetting.PaperOrientation == PaperOrientation.横向; int paperType = rd.PageSetting.GetNPOIPaperType(); if (paperType == 0) //自定义,不清楚NPOI中怎样设置,用A4 { sheet.PrintSetup.PaperSize = 9; } else if (paperType == -1) //未打到对应关系 { sheet.PrintSetup.PaperSize = 9; } else { sheet.PrintSetup.PaperSize = (short)paperType; } sheet.IsPrintGridlines = true; sheet.FitToPage = false;//默认值是true //设置锁定 sheet.ProtectSheet("{D49C4D85-F46E-456F-9C71-DB7D880B5B04}"); sheet.IsPrintGridlines = false; //不设置这个,打印时会打印出空白单元格的网络线(即使没有网格线也会打印) sheetIndex++; } string tmpFileName = getTmpFileName(); using (FileStream file = new FileStream(tmpFileName, FileMode.Create)) { workbook.Write(file); file.Close(); } I3PCUtil.CreateAndWaitProcessByEvent(null, tmpFileName, "", IntPtr.Zero, 0, 0); }
protected void btnImport_Click(object sender, EventArgs e) { string strPrintId = this.strPrintId.Value; TMisMonitorSubtaskVo objSubTask = new TMisMonitorSubtaskVo(); objSubTask.TASK_ID = strPrintId; DataTable dtSub = new TMisMonitorSubtaskLogic().SelectByTable(objSubTask); string strSampleIDs = ""; for (int i = 0; i < dtSub.Rows.Count; i++) { GetPoint_UnderTask(dtSub.Rows[i]["ID"].ToString(), ref strSampleIDs); } //获取基本信息 DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleIDs, "021", "0"); int iPageCount = dt.Rows.Count / 17; if (dt.Rows.Count % 17 != 0) { iPageCount += 1; } FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/QHDSamplingCode.xls"), FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); //sheet复制 for (int k = 1; k < iPageCount; k++) { hssfworkbook.CloneSheet(0); hssfworkbook.SetSheetName(k, "Sheet" + (k + 1).ToString()); } for (int m = 1; m <= iPageCount; m++) { ISheet sheet = hssfworkbook.GetSheet("Sheet" + m.ToString()); sheet.GetRow(23).GetCell(0).SetCellValue("样品交接数量: " + dt.Rows.Count.ToString()); sheet.GetRow(2).GetCell(0).SetCellValue(string.Format(" 采 样 日 期: {0} 年 {1} 月 {2} 日", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString())); sheet.GetRow(3).GetCell(0).SetCellValue(string.Format(" 样品交接日期: {0} 年 {1} 月 {2} 日 {3} 点 {4} 分", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Hour.ToString(), DateTime.Now.Minute.ToString())); sheet.GetRow(1).GetCell(6).SetCellValue(string.Format("第 {0} 页 共 {1} 页", m.ToString(), iPageCount.ToString())); DataTable dtNew = new DataTable(); dtNew = dt.Copy(); dtNew.Clear(); for (int n = (m - 1) * 17; n < m * 17; n++) { if (n >= dt.Rows.Count) { break; } dtNew.ImportRow(dt.Rows[n]); } for (int i = 0; i < dtNew.Rows.Count; i++) { string strItmeNum = ""; TMisMonitorResultVo objResult = new TMisMonitorResultVo(); objResult.SAMPLE_ID = dtNew.Rows[i]["ID"].ToString(); DataTable dtResult = new TMisMonitorResultLogic().SelectByTable(objResult); for (int j = 0; j < dtResult.Rows.Count; j++) { TBaseItemInfoVo objItem = new TBaseItemInfoVo(); objItem.ID = dtResult.Rows[j]["ITEM_ID"].ToString(); objItem.IS_SAMPLEDEPT = "否"; DataTable dtItem = new TBaseItemInfoLogic().SelectByTable(objItem); if (dtItem.Rows.Count > 0 && dtItem.Rows[0]["ITEM_NUM"].ToString().Length > 0) { strItmeNum += (strItmeNum.Length > 0) ? "," + dtItem.Rows[0]["ITEM_NUM"].ToString() : dtItem.Rows[0]["ITEM_NUM"].ToString(); } } sheet.GetRow(i + 6).GetCell(0).SetCellValue((i + 1).ToString()); sheet.GetRow(i + 6).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_CODE"].ToString()); sheet.GetRow(i + 6).GetCell(6).SetCellValue(strItmeNum); } } using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("水质样品交接记录表.xls", Encoding.UTF8)); curContext.Response.BinaryWrite(stream.GetBuffer()); curContext.Response.End(); } }
//刷新表注释 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); } }
private void Recover(string fpSrc, string fpNew) { var si = new MemoryStream(File.ReadAllBytes(fpSrc)); var wb = new HSSFWorkbook(si); var wb2 = new HSSFWorkbook(); var df = wb.CreateDataFormat(); var df2 = wb2.CreateDataFormat(); SortedDictionary <int, ICellStyle> csMap = new SortedDictionary <int, ICellStyle>(); SortedDictionary <int, IFont> csF = new SortedDictionary <int, IFont>(); for (int z = 0; z < wb.NumberOfSheets; z++) { var sh = wb.GetSheetAt(z); var sh2 = new HSSFSheet(wb2); wb2.Add(sh2); wb2.SetSheetName(z, sh.SheetName); var y0 = sh.FirstRowNum; var y1 = sh.LastRowNum; SortedDictionary <int, int> useX = new SortedDictionary <int, int>(); for (int t = 0; t < sh.NumMergedRegions; t++) { sh2.AddMergedRegion(sh.GetMergedRegion(t)); } for (int y = y0; y <= y1; y++) { var row = sh.GetRow(y); if (row == null) { continue; } var row2 = sh2.CreateRow(y); row2.Height = row.Height; var x0 = row.FirstCellNum; var x1 = row.LastCellNum; if (x0 < 0) { continue; } for (int x = x0; x <= x1; x++) { useX[x] = 0; var cell = row.GetCell(x); if (cell == null) { continue; } var cell2 = row2.CreateCell(x, cell.CellType); switch (cell.CellType) { case CellType.Boolean: cell2.SetCellValue(cell.BooleanCellValue); break; case CellType.Numeric: cell2.SetCellValue(cell.NumericCellValue); break; case CellType.String: cell2.SetCellValue(cell.StringCellValue); break; case CellType.Formula: cell2.SetCellFormula(cell.CellFormula); //d["" + cell.CellFormula] = ""; break; } { ICellStyle ns = null; { int iSrc = cell.CellStyle.Index; if (!csMap.TryGetValue(iSrc, out ns)) { ns = wb2.CreateCellStyle(); csMap[iSrc] = ns; } } IFont font2 = null; { int iSrc = cell.CellStyle.FontIndex; if (!csF.TryGetValue(iSrc, out font2)) { font2 = wb2.CreateFont(); var font = cell.CellStyle.GetFont(wb); font2.IsBold = font.IsBold; font2.Charset = font.Charset; font2.Color = font.Color; font2.FontHeight = font.FontHeight; font2.FontName = font.FontName; font2.IsBold = font.IsBold; font2.IsItalic = font.IsItalic; font2.IsStrikeout = font.IsStrikeout; font2.TypeOffset = font.TypeOffset; font2.Underline = font.Underline; csF[iSrc] = font2; } } ns.Alignment = cell.CellStyle.Alignment; ns.BorderBottom = cell.CellStyle.BorderBottom; ns.BorderDiagonal = cell.CellStyle.BorderDiagonal; ns.BorderDiagonalColor = cell.CellStyle.BorderDiagonalColor; ns.BorderDiagonalLineStyle = cell.CellStyle.BorderDiagonalLineStyle; ns.BorderLeft = cell.CellStyle.BorderLeft; ns.BorderRight = cell.CellStyle.BorderRight; ns.BorderTop = cell.CellStyle.BorderTop; ns.BottomBorderColor = cell.CellStyle.BottomBorderColor; ns.DataFormat = df2.GetFormat(cell.CellStyle.GetDataFormatString() ?? GetBuiltinFormat(cell.CellStyle.DataFormat) ); ns.FillBackgroundColor = cell.CellStyle.FillBackgroundColor; ns.FillForegroundColor = cell.CellStyle.FillForegroundColor; ns.FillPattern = cell.CellStyle.FillPattern; ns.SetFont(font2); ns.Indention = cell.CellStyle.Indention; ns.IsHidden = cell.CellStyle.IsHidden; ns.IsLocked = cell.CellStyle.IsLocked; ns.LeftBorderColor = cell.CellStyle.LeftBorderColor; ns.RightBorderColor = cell.CellStyle.RightBorderColor; ns.Rotation = cell.CellStyle.Rotation; ns.ShrinkToFit = cell.CellStyle.ShrinkToFit; ns.TopBorderColor = cell.CellStyle.TopBorderColor; ns.VerticalAlignment = cell.CellStyle.VerticalAlignment; ns.WrapText = cell.CellStyle.WrapText; cell2.CellStyle = ns; } } } foreach (var x in useX.Keys) { sh2.SetColumnWidth(x, sh.GetColumnWidth(x)); } } using (var os = File.Create(fpNew)) { wb2.Write(os); } }
/// <summary> /// 根据收货出账单号生成Execl /// </summary> /// <param name="bill"></param> /// <param name="list"></param> public static void ExeclCreate(ReceivingBill bill, List <BillModel> list) { Log.Info("LMS开始生成账单号为:{0}execl表格".FormatWith(bill.ReceivingBillID)); var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new { ShippingMethodName = g.Key, TotalFee = g.Sum(p => p.Freight + p.FuelCharge + p.Register + p.Surcharge + p.TariffPrepayFee + p.SpecialFee + p.RemoteAreaFee) }).ToList(); using ( var file = new FileStream( AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open, FileAccess.Read)) { IWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheetNew = hssfworkbook.CloneSheet(0); sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称:{0}".FormatWith(bill.CustomerName)); sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间:{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd"))); IRow sourceRow = sheetNew.GetRow(2); int startRow = 2; foreach (var t in groupShipping) { startRow++; sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true); IRow row = sheetNew.CreateRow(startRow); CopyRow(sourceRow, ref row); row.GetCell(0).SetCellValue(t.ShippingMethodName); row.GetCell(1).SetCellValue("总费用:{0}".FormatWith(t.TotalFee.ToString("F2"))); } startRow++; sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true); IRow srow = sheetNew.CreateRow(startRow); CopyRow(sourceRow, ref srow); srow.GetCell(0).SetCellValue("结算人:{0}".FormatWith(bill.ReceivingBillAuditor)); startRow = startRow + 3; int startR = startRow; foreach (var b in list) { IRow row = sheetNew.CreateRow(startRow); row.CreateCell(0).SetCellValue(b.WayBillNumber); row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? ""); row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm")); row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm")); row.CreateCell(4).SetCellValue(b.TrackingNumber ?? ""); row.CreateCell(5).SetCellValue(b.ChineseName); row.CreateCell(6).SetCellValue(b.InShippingMethodName); row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4")); row.CreateCell(8).SetCellValue(b.Weight.ToString("F4")); row.CreateCell(9).SetCellValue(b.CountNumber); row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2"))); row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2"))); row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2"))); row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2"))); row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2"))); row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2"))); row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2"))); startRow++; row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")"); } IRow totalRow = sheetNew.CreateRow(startRow); for (int i = 0; i < 18; i++) { totalRow.CreateCell(i).SetCellValue(""); } startR++; totalRow.Cells[9].SetCellValue("各项计费小计:"); totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")"); totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")"); totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")"); totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")"); totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")"); totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")"); totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")"); totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")"); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls"))) { File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls")); } var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create); hssfworkbook.RemoveSheetAt(0); hssfworkbook.Write(savefile); file.Close(); } Log.Info("LMS完成生成账单号为:{0}execl表格".FormatWith(bill.ReceivingBillID)); }