private static void SetCellValue(NPOI.SS.UserModel.Cell cell, NPOI.SS.UserModel.CellValue cv) { NPOI.SS.UserModel.CellType cellType = cv.CellType; switch (cellType) { case NPOI.SS.UserModel.CellType.BOOLEAN: cell.SetCellValue(cv.BooleanValue); break; case NPOI.SS.UserModel.CellType.ERROR: cell.CellErrorValue = cv.ErrorValue; break; case NPOI.SS.UserModel.CellType.NUMERIC: cell.SetCellValue(cv.NumberValue); break; case NPOI.SS.UserModel.CellType.STRING: cell.SetCellValue(new HSSFRichTextString(cv.StringValue)); break; //case NPOI.SS.UserModel.CellType.BLANK: //// never happens - blanks eventually get translated to zero //case NPOI.SS.UserModel.CellType.FORMULA: //// this will never happen, we have already evaluated the formula default: throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")"); } }
/// <summary> /// Sets the value. /// </summary> /// <param name="rowPos">The row pos.</param> /// <param name="columnPos">The column pos.</param> /// <param name="value">The value.</param> /// <param name="header">if set to <c>true</c> [header].</param> public void SetValue(int rowPos, int columnPos, string value, bool header) { NPOI.SS.UserModel.Cell cell = GetCell(rowPos, columnPos); if (value == null) { value = ""; } //Setting value to the cell cell.SetCellValue(value); if (_headerStyle != null && _dataStyle != null) { if (header) { cell.RichStringCellValue.ApplyFont(_headerStyle.GetFont(_workbook)); } else { cell.RichStringCellValue.ApplyFont(_dataStyle.GetFont(_workbook)); } } }
private void CreateRow(IEnumerable <Cell> row, Row currentRow) { int columnOrdinal = 0; foreach (var cell in row) { if (cell.ColumnSpan > 1) { int rangeStartColumn = columnOrdinal; for (int i = 0; i < cell.ColumnSpan; i++) { NPOI.SS.UserModel.Cell current = CreateCell(cell, currentRow, columnOrdinal); if (i == 0) { current.SetCellValue(cell.Value); } columnOrdinal++; } var cra = new CellRangeAddress(currentRow.RowNum, currentRow.RowNum, rangeStartColumn, rangeStartColumn + (cell.ColumnSpan - 1)); sheet.AddMergedRegion(cra); } else { CreateCell(cell, currentRow, columnOrdinal).SetCellValue(cell.Value); columnOrdinal++; } } }
/// <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 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> /// Sets the number in the specified position. /// </summary> /// <param name="rowPos">The row pos.</param> /// <param name="columnPos">The column pos.</param> /// <param name="value">The value.</param> public void SetNumber(int rowPos, int columnPos, double value) { NPOI.SS.UserModel.Cell cell = GetCell(rowPos, columnPos); cell.SetCellValue(value); }
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); }
public ExcelHelper SetCellValue(string val) { _cell.SetCellValue(val); return(this); }