/** * @return <c>null</c> if cell is1 missing, empty or blank */ private static String GetTargetFunctionName(NPOI.SS.UserModel.Row r) { if (r == null) { Console.Error.WriteLine("Warning - given null row, can't figure out function name"); return(null); } Cell cell = r.GetCell(SS.COLUMN_INDEX_FUNCTION_NAME); if (cell == null) { Console.Error.WriteLine("Warning - NPOI.SS.UserModel.Row " + r.RowNum + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name"); return(null); } if (cell.CellType == NPOI.SS.UserModel.CellType.BLANK) { return(null); } if (cell.CellType == NPOI.SS.UserModel.CellType.STRING) { return(cell.RichStringCellValue.String); } throw new AssertFailedException("Bad cell type for 'function name' column: (" + cell.CellType + ") row (" + (r.RowNum + 1) + ")"); }
private static Cell GetExpectedValueCell(NPOI.SS.UserModel.Row row, short columnIndex) { if (row == null) { return(null); } return(row.GetCell(columnIndex)); }
/// <summary> /// Get a specific cell from a row. If the cell doesn't exist, /// </summary> /// <param name="row">The row that the cell is part of</param> /// <param name="column">The column index that the cell is in.</param> /// <returns>The cell indicated by the column.</returns> public static NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Row row, int column) { NPOI.SS.UserModel.Cell cell = row.GetCell(column); if (cell == null) { cell = row.CreateCell(column); } return(cell); }
/// <summary> /// Get a row from the spreadsheet, and Create it if it doesn't exist. /// </summary> /// <param name="rowCounter">The 0 based row number</param> /// <param name="sheet">The sheet that the row is part of.</param> /// <returns>The row indicated by the rowCounter</returns> public static NPOI.SS.UserModel.Row GetRow(int rowCounter, HSSFSheet sheet) { NPOI.SS.UserModel.Row row = sheet.GetRow(rowCounter); if (row == null) { row = sheet.CreateRow(rowCounter); } return(row); }
/// <summary> /// 把DataTable 转为Excel 内容 /// </summary> /// <param name="dt"></param> /// <param name="startRow"></param> /// <param name="endRow"></param> /// <param name="book"></param> /// <param name="sheetName"></param> private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName) { //头部样式 CellStyle headstyle = book.CreateCellStyle(); headstyle.Alignment = HorizontalAlignment.CENTER; headstyle.VerticalAlignment = VerticalAlignment.CENTER; Font headfont = book.CreateFont(); headfont.Boldweight = 700; headstyle.SetFont(headfont); headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND; headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; headstyle.BorderBottom = CellBorderType.THIN; headstyle.BorderLeft = CellBorderType.THIN; headstyle.BorderRight = CellBorderType.THIN; headstyle.BorderTop = CellBorderType.THIN; NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName); NPOI.SS.UserModel.Row header = sheet.CreateRow(0); header.Height = 20 * 20; //表格内容样 CellStyle dataStyle = book.CreateCellStyle(); dataStyle.BorderBottom = CellBorderType.THIN; dataStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; dataStyle.BorderLeft = CellBorderType.THIN; dataStyle.BorderRight = CellBorderType.THIN; dataStyle.BorderTop = CellBorderType.THIN; for (int i = 0; i < dt.Columns.Count; i++) { NPOI.SS.UserModel.Cell cell = header.CreateCell(i); cell.CellStyle = headstyle; string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName; cell.SetCellValue(val); } int rowIndex = 1; for (int i = startRow; i <= endRow; i++) { DataRow dtRow = dt.Rows[i]; NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++); for (int j = 0; j < dtRow.ItemArray.Length; j++) { excelRow.CreateCell(j).CellStyle = dataStyle; excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString()); } } }
/// <summary> /// Creates a cell, gives it a value, and applies a style if provided /// </summary> /// <param name="row">the row to Create the cell in</param> /// <param name="column">the column index to Create the cell in</param> /// <param name="value">The value of the cell</param> /// <param name="style">If the style is not null, then Set</param> /// <returns>A new HSSFCell</returns> public static NPOI.SS.UserModel.Cell CreateCell(NPOI.SS.UserModel.Row row, int column, String value, HSSFCellStyle style) { NPOI.SS.UserModel.Cell cell = GetCell(row, column); cell.SetCellValue(new HSSFRichTextString(value)); if (style != null) { cell.CellStyle = (style); } return(cell); }
public DataTable GetData(string filePath) { HSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion using (NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0)) { DataTable table = new DataTable(); NPOI.SS.UserModel.Row headerRow = sheet.GetRow(0); //第一行为标题行 int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1 //handling header. for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { NPOI.SS.UserModel.Row row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = GetCellValue(row.GetCell(j)); } } } table.Rows.Add(dataRow); } return(table); } }
protected void CreateDataHeader(Sheet worksheet) { StyleManager.InitStaticStyle(); worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8)); worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 2)); worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5)); worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 6, 8)); int colindex = 0; NPOI.SS.UserModel.Row header = worksheet.CreateRow(0); StyleManager.SetCenterBoldStringCell(header.CreateCell(colindex++)).SetCellValue("Daily AR&AP Interface"); header.Height = 450; NPOI.SS.UserModel.Row header1 = worksheet.CreateRow(1); colindex = 0; StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Newegg.com.cn"); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Posting Date: " + end); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("MTD( " + begin + " - " + end + " )"); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty); NPOI.SS.UserModel.Row header2 = worksheet.CreateRow(2); colindex = 0; StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data"); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table"); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance"); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data"); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table"); StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance"); NPOI.SS.UserModel.Row header3 = worksheet.CreateRow(3); colindex = 0; StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("AcctType"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("DocumentType"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("CompanyCode"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)"); StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)"); }
/** * @param startRowIndex row index in the spReadsheet where the first function/operator is1 found * @param testFocusFunctionName name of a single function/operator to test alone. * Typically pass <c>null</c> to test all functions */ private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) { HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook); int rowIndex = startRowIndex; while (true) { NPOI.SS.UserModel.Row r = sheet.GetRow(rowIndex); String targetFunctionName = GetTargetFunctionName(r); if (targetFunctionName == null) { throw new AssertFailedException("Test spReadsheet cell empty on row (" + (rowIndex + 1) + "). Expected function name or '" + SS.FUNCTION_NAMES_END_SENTINEL + "'"); } if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL)) { // found end of functions list break; } if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.InvariantCultureIgnoreCase)) { // expected results are on the row below NPOI.SS.UserModel.Row expectedValuesRow = sheet.GetRow(rowIndex + 1); if (expectedValuesRow == null) { int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row throw new AssertFailedException("Missing expected values row for function '" + targetFunctionName + " (row " + missingRowNum + ")"); } switch (ProcessFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) { case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break; case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break; case Result.NO_EVALUATIONS_FOUND: // do nothing break; default: throw new Exception("unexpected result"); } } rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION; } }
protected void CreateDataCells(Sheet worksheet, List <SAPInterfaceExchangeInfo> dataList) { ushort currentRowIndex = 4; ushort currentColIndex = 0; dataList.ForEach(delegate(SAPInterfaceExchangeInfo item) { NPOI.SS.UserModel.Row data = worksheet.CreateRow(currentRowIndex++); currentColIndex = 0; StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.AcctTypeDisplay); StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.DocumentType); StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.CompanyCode); StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.Legacy_GLAmount)); StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.SAP_GLAmount)); StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.DateBalance)); StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDLegacy_GLAmount)); StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDSAP_GLAmount)); StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDBalance)); }); }
/// <summary> /// Gets the cell to manage it. /// </summary> /// <param name="rowPos">The row pos.</param> /// <param name="columnPos">The column pos.</param> /// <returns>Cell of the spreadsheet</returns> public NPOI.SS.UserModel.Cell GetCell(int rowPos, int columnPos) { NPOI.SS.UserModel.Row row = _sheet.GetRow(rowPos); if (row == null) { AddRow(rowPos); row = _sheet.GetRow(rowPos); } //Creating the cell NPOI.SS.UserModel.Cell cell = row.GetCell(columnPos); if (cell == null) { row.CreateCell(columnPos); cell = row.GetCell(columnPos); } return(cell); }
/** * * @return a constant from the local Result class denoting whether there were any evaluation * cases, and whether they all succeeded. */ private int ProcessFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, NPOI.SS.UserModel.Row formulasRow, NPOI.SS.UserModel.Row expectedValuesRow) { int result = Result.NO_EVALUATIONS_FOUND; // so far int endcolnum = formulasRow.LastCellNum; //evaluator.SetCurrentRow(formulasRow); // iterate across the row for all the evaluation cases for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) { Cell c = formulasRow.GetCell(colnum); if (c == null || c.CellType != NPOI.SS.UserModel.CellType.FORMULA) { continue; } NPOI.SS.UserModel.CellValue actualValue = evaluator.Evaluate(c); Cell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum); try { ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum, expectedValueCell, actualValue); _evaluationSuccessCount++; if (result != Result.SOME_EVALUATIONS_FAILED) { result = Result.ALL_EVALUATIONS_SUCCEEDED; } } catch (AssertFailedException) { _evaluationFailureCount++; //printShortStackTrace(System.err, e); result = Result.SOME_EVALUATIONS_FAILED; } } return(result); }
public void TestCountifFromSpreadsheet() { String FILE_NAME = "countifExamples.xls"; int START_ROW_IX = 1; int COL_IX_ACTUAL = 2; int COL_IX_EXPECTED = 3; int failureCount = 0; HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(FILE_NAME); NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); int maxRow = sheet.LastRowNum; for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++) { NPOI.SS.UserModel.Row row = sheet.GetRow(rowIx); if (row == null) { continue; } Cell cell = row.GetCell(COL_IX_ACTUAL); NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell); double actualValue = cv.NumberValue; double expectedValue = row.GetCell(COL_IX_EXPECTED).NumericCellValue; if (actualValue != expectedValue) { Console.Error.WriteLine("Problem with Test case on row " + (rowIx + 1) + " " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") "); failureCount++; } } if (failureCount > 0) { throw new AssertFailedException(failureCount + " countif evaluations failed. See stderr for more details"); } }
protected void ExportResult(IList <Bill> billList) { HSSFWorkbook excel = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = excel.CreateSheet("BILL"); NPOI.SS.UserModel.Row row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("BILL NO"); row.CreateCell(1).SetCellValue("ASN"); row.CreateCell(2).SetCellValue("零件"); row.CreateCell(3).SetCellValue("单价"); row.CreateCell(4).SetCellValue("开票数"); row.CreateCell(5).SetCellValue("金额"); int rowNum = 1; foreach (Bill bill in billList) { foreach (BillDetail bd in bill.BillDetails) { NPOI.SS.UserModel.Row _row = sheet.CreateRow(rowNum); _row.CreateCell(0).SetCellValue(bd.Bill.BillNo); _row.CreateCell(1).SetCellValue(bd.IpNo); _row.CreateCell(2).SetCellValue(bd.ActingBill.Item.Code); _row.CreateCell(3).SetCellValue((double)bd.UnitPrice); _row.CreateCell(4).SetCellValue((double)bd.BilledQty); _row.CreateCell(5).SetCellValue((double)bd.Amount); rowNum++; } } MemoryStream ms = new MemoryStream(); excel.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment;filename=BillResult.xls")); Response.BinaryWrite(ms.ToArray()); excel = null; ms.Close(); ms.Dispose(); }
public override void OutPut(DataTable dt) { string fileExt = DateTime.Now.ToString("yyyyMMddHHmmss"); string fileOutPut = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt); string saveFileName = fileOutPut; //SaveFileDialog saveDialog = new SaveFileDialog(); //saveDialog.DefaultExt = "xls"; //saveDialog.Filter = "Excel文件|*.xls"; //saveDialog.FileName = fileName; //saveDialog.ShowDialog(); //saveFileName = saveDialog.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); Sheet sheet = workbook.CreateSheet("Sheet1"); NPOI.SS.UserModel.Row dataTableName = sheet.CreateRow(0); NPOI.SS.UserModel.Cell cellTableName = dataTableName.CreateCell(0); cellTableName.SetCellValue(dt.TableName); //sheet.SetActiveCellRange(0, 2, 0, dt.Columns.Count); int rangeID = sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, dt.Columns.Count)); NPOI.SS.UserModel.Row dataRowColumnName = sheet.CreateRow(3); for (int j = 0; j < dt.Columns.Count; j++) { NPOI.SS.UserModel.Cell cellName = dataRowColumnName.CreateCell(j); cellName.SetCellValue(dt.Columns[j].ColumnName); NPOI.SS.UserModel.Name sheetName = workbook.CreateName(); sheetName.NameName = dt.Columns[j].ColumnName; sheetName.SheetIndex = 0; } int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; for (int i = 0; i < rowCount; i++) { NPOI.SS.UserModel.Row dataRow = sheet.CreateRow(4 + i); for (int j = 0; j < colCount; j++) { NPOI.SS.UserModel.Cell cell = dataRow.CreateCell(j); if (dt.Rows[i][j].GetType().Equals(typeof(DateTime))) { cell.SetCellValue(((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss")); } else { cell.SetCellValue(dt.Rows[i][j].ToString()); //项目序号 } } } workbook.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); if (MessageBox.Show("Excel导出成功:" + fileOutPut + "\r\n是否要打开?", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { System.Diagnostics.Process.Start(fileOutPut); } }
/// <summary> /// Adds the row into sheet. /// </summary> /// <param name="pos">The row position.</param> public void AddRow(int pos) { //Creating row to insert data entries NPOI.SS.UserModel.Row row = _sheet.CreateRow(pos); }
/// <summary> /// Create a cell, and give it a value. /// </summary> /// <param name="row">the row to Create the cell in</param> /// <param name="column">the column index to Create the cell in</param> /// <param name="value">The value of the cell</param> /// <returns>A new HSSFCell.</returns> public static NPOI.SS.UserModel.Cell CreateCell(NPOI.SS.UserModel.Row row, int column, String value) { return(CreateCell(row, column, value, null)); }
/// <summary> /// 根据Excel获取集合信息 /// </summary> /// <param name="fileName">Excel存储路径</param> /// <returns></returns> public List <IMovieShowList.MovieShow> GetList4Excel(string fileName) { List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>(); HSSFWorkbook wk = null; using (FileStream fs = System.IO.File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { //把xls文件读入workbook变量里,之后就可以关闭了 wk = new HSSFWorkbook(fs); fs.Close(); } HSSFSheet sheet = (HSSFSheet)wk.GetSheetAt(0); if (sheet != null) { int rowIndex = 0; NPOI.SS.UserModel.Row row = null; while ((row = sheet.GetRow(rowIndex)) != null) { try { Cell c1 = row.GetCell(0); string room = c1.RichStringCellValue.String; Cell c2 = row.GetCell(1); string tim = c2.RichStringCellValue.String; Cell c3 = row.GetCell(2); string name = c3.RichStringCellValue.String; if (string.IsNullOrWhiteSpace(room) || string.IsNullOrWhiteSpace(tim) || string.IsNullOrWhiteSpace(name)) { rowIndex++; continue; } string time = ParseBeginTime(tim); list.Add(new IMovieShowList.MovieShow() { Room = room, BeginTime = time, Name = name }); rowIndex++; } catch (Exception ex) { isOk = false; Msg = "xcel文件中的时间有错误,在" + (rowIndex + 1) + "行" + Environment.NewLine + ex.Message; break; } } sheet.Dispose(); } return(list); }
public static void ExportToExcelExtended(DataTable table, string Name) { //ExcelHelper excel = new ExcelHelper(); //excel.CreateFile(Name); //excel.CreateSheet(Name); InitializeWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("new sheet"); //ExcelDocument document = new ExcelDocument(); //document.UserName = "******"; //document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage; //document.ColumnWidth(0, 120); //document.ColumnWidth(1, 80); //document[0, 0].Value = Name; //document[0, 0].Font = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold); //document[0, 0].ForeColor = ExcelColor.DarkRed; //document[0, 0].Alignment = Alignment.Centered; //document[0, 0].BackColor = ExcelColor.Silver; int i = 1; //стрічка int j = 1; //колонка NPOI.SS.UserModel.Row header = sheet.CreateRow(0); foreach (DataColumn column in table.Columns) { NPOI.SS.UserModel.Cell cell = header.CreateCell(j); cell.SetCellValue(column.ColumnName); j++; } j = 1; foreach (DataRow r in table.Rows) { j = 1; //NPOI.SS.UserModel.Row row = excel.CreateRow(i); NPOI.SS.UserModel.Row row = sheet.CreateRow(i); foreach (DataColumn column in table.Columns) { NPOI.SS.UserModel.Cell cell = row.CreateCell(j); Type dataType = column.DataType; switch (dataType.Name) { case "String": { cell.SetCellValue(r.ItemArray[j - 1].ToString()); break; } case "DateTime": { DateTime date = (Convert.ToDateTime(r.ItemArray[j - 1])); cell.SetCellValue(date); CellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyyy-MM-dd h:mm:ss"); cell.CellStyle = cellStyle; break; } case "Decimal": { if (r.ItemArray[j - 1] != null) { if (r.ItemArray[j - 1] != null) { if (r.ItemArray[j - 1].ToString().Length > 0) { cell.SetCellValue(Convert.ToDouble(r.ItemArray[j - 1])); } } } break; } default: { cell.SetCellValue(r.ItemArray[j - 1].ToString()); break; } } //excel.SetCellValue( r.ItemArray[j] j++; } //foreach (object item in r.ItemArray) //{ // //document.Cell(i,j).Format // excel.CreateCell(j); // excel.SetCellValue( item == null ? "" : (item); // j++; //} i++; } SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.FileName = Name; saveFileDialog1.DefaultExt = "xls"; saveFileDialog1.ShowDialog(); //FileStream stream = new FileStream(saveFileDialog1.FileName, FileMode.Create); WriteToFile(saveFileDialog1.FileName); //System.Diagnostics.Process.Start(saveFileDialog1.FileName); }
/// <summary> /// 导出DataTable到Excel文件 /// </summary> /// <param name="filename">文件名称</param> /// <param name="dtTable">数据DataTable</param> /// <returns>文件保存路径</returns> public string ExportFile(string filename, List <dynamic> data, string webPath = "") { NPOI.SS.UserModel.Row rowItem = null; int rowCnt = 0; foreach (dynamic dr in data) { if (rowCnt == 0) { //创建表头 rowItem = _sheet.CreateRow(rowCnt); int i = 0; foreach (KeyValuePair <string, object> dynamicItem in dr) { if (_colName.ContainsKey(dynamicItem.Key)) { rowItem.CreateCell(i).SetCellValue(_colName[dynamicItem.Key]); } else { rowItem.CreateCell(i).SetCellValue(dynamicItem.Key); } i++; } rowCnt = rowCnt + 1; } rowItem = _sheet.CreateRow(rowCnt); int k = 0; foreach (KeyValuePair <string, object> dynamicItem in dr) { string sItemVal = ""; if (dynamicItem.Value != null) { sItemVal = dynamicItem.Value.ToString().Trim(); } rowItem.CreateCell(k).SetCellValue(sItemVal); k++; } rowCnt = rowCnt + 1; } //保存文件 if (webPath == "") { webPath = System.Web.HttpContext.Current.Server.MapPath("/"); } //文件名 string strFileName = string.Format("{0}_{1}_{2}.xls", filename.ToString(), DateTime.Now.ToString("yyyyMMddHHmm"), CommonLib.Helper.GetRandomNum()); //文件路径 string strFilePath = "\\ExportFile\\" + _className + "\\" + DateTime.Now.ToString("yyyyMM") + "\\"; if (!Directory.Exists(webPath + strFilePath)) { Directory.CreateDirectory(webPath + strFilePath); } var fileInfo = new FileStream(webPath + strFilePath + strFileName, FileMode.Create); Workbook.Write(fileInfo); fileInfo.Close(); return(strFilePath + strFileName); }