/// <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)); } } }
/** * This method attempt to find an already existing HSSFCellStyle that matches * what you want the style to be. If it does not find the style, then it * Creates a new one. If it does Create a new one, then it applies the * propertyName and propertyValue to the style. This is necessary because * Excel has an upper limit on the number of Styles that it supports. * *@param workbook The workbook that is being worked with. *@param propertyName The name of the property that is to be * changed. *@param propertyValue The value of the property that is to be * changed. *@param cell The cell that needs it's style changes *@exception NestableException Thrown if an error happens. */ public static void SetCellStyleProperty(NPOI.SS.UserModel.Cell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue) { NPOI.SS.UserModel.CellStyle originalStyle = cell.CellStyle; NPOI.SS.UserModel.CellStyle newStyle = null; Hashtable values = GetFormatProperties(originalStyle); values[propertyName] = propertyValue; // index seems like what index the cellstyle is in the list of styles for a workbook. // not good to compare on! short numberCellStyles = workbook.NumCellStyles; for (short i = 0; i < numberCellStyles; i++) { NPOI.SS.UserModel.CellStyle wbStyle = workbook.GetCellStyleAt(i); Hashtable wbStyleMap = GetFormatProperties(wbStyle); if (wbStyleMap.Equals(values)) { newStyle = wbStyle; break; } } if (newStyle == null) { newStyle = workbook.CreateCellStyle(); SetFormatProperties(newStyle, workbook, values); } cell.CellStyle = (newStyle); }
/** * If cell Contains a formula, the formula is Evaluated and returned, * else the CellValue simply copies the appropriate cell value from * the cell and also its cell type. This method should be preferred over * EvaluateInCell() when the call should not modify the contents of the * original cell. * @param cell */ /** * If cell contains a formula, the formula is evaluated and returned, * else the CellValue simply copies the appropriate cell value from * the cell and also its cell type. This method should be preferred over * evaluateInCell() when the call should not modify the contents of the * original cell. * * @param cell may be <c>null</c> signifying that the cell is not present (or blank) * @return <c>null</c> if the supplied cell is <c>null</c> or blank */ public NPOI.SS.UserModel.CellValue Evaluate(NPOI.SS.UserModel.Cell cell) { if (cell == null) { return(null); } switch (cell.CellType) { case NPOI.SS.UserModel.CellType.BOOLEAN: return(NPOI.SS.UserModel.CellValue.ValueOf(cell.BooleanCellValue)); case NPOI.SS.UserModel.CellType.ERROR: return(NPOI.SS.UserModel.CellValue.GetError(cell.ErrorCellValue)); case NPOI.SS.UserModel.CellType.FORMULA: return(EvaluateFormulaCellValue(cell)); case NPOI.SS.UserModel.CellType.NUMERIC: return(new NPOI.SS.UserModel.CellValue(cell.NumericCellValue)); case NPOI.SS.UserModel.CellType.STRING: return(new NPOI.SS.UserModel.CellValue(cell.RichStringCellValue.String)); case NPOI.SS.UserModel.CellType.BLANK: return(null); } throw new InvalidOperationException("Bad cell type (" + cell.CellType + ")"); }
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> /// Removes the cell. /// </summary> /// <param name="cell">The cell.</param> /// <param name="alsoRemoveRecords">if set to <c>true</c> [also remove records].</param> private void RemoveCell(NPOI.SS.UserModel.Cell cell, bool alsoRemoveRecords) { int column = cell.ColumnIndex; if (column < 0) { throw new Exception("Negative cell indexes not allowed"); } //if (column >= cells.Count || cell != cells[column]) if (!cells.ContainsKey(column) || cell != cells[column]) { throw new Exception("Specified cell is not from this row"); } cells.Remove(column); if (alsoRemoveRecords) { CellValueRecordInterface cval = ((HSSFCell)cell).CellValueRecord; sheet.Sheet.RemoveValueRecord(RowNum, cval); } if (cell.ColumnIndex + 1 == row.LastCol) { row.LastCol = FindLastCell(row.LastCol) + 1; } if (cell.ColumnIndex == row.FirstCol) { row.FirstCol = FindFirstCell(row.FirstCol); } }
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++; } } }
/** * Returns a CellValue wrapper around the supplied ValueEval instance. * @param eval */ private NPOI.SS.UserModel.CellValue EvaluateFormulaCellValue(NPOI.SS.UserModel.Cell cell) { ValueEval eval = _bookEvaluator.Evaluate(new HSSFEvaluationCell((HSSFCell)cell)); if (eval is NumberEval) { NumberEval ne = (NumberEval)eval; return(new NPOI.SS.UserModel.CellValue(ne.NumberValue)); } if (eval is BoolEval) { BoolEval be = (BoolEval)eval; return(NPOI.SS.UserModel.CellValue.ValueOf(be.BooleanValue)); } if (eval is StringEval) { StringEval ne = (StringEval)eval; return(new NPOI.SS.UserModel.CellValue(ne.StringValue)); } if (eval is ErrorEval) { return(NPOI.SS.UserModel.CellValue.GetError(((ErrorEval)eval).ErrorCode)); } throw new InvalidOperationException("Unexpected eval class (" + eval.GetType().Name + ")"); }
/// <summary> /// Get the hssfcell representing a given column (logical cell) /// 0-based. If you ask for a cell that is not defined, then /// your supplied policy says what to do /// </summary> /// <param name="cellnum">0 based column number</param> /// <param name="policy">Policy on blank / missing cells</param> /// <returns>that column or null if Undefined + policy allows.</returns> public Cell GetCell(int cellnum, MissingCellPolicy policy) { NPOI.SS.UserModel.Cell cell = RetrieveCell(cellnum); if (policy == MissingCellPolicy.RETURN_NULL_AND_BLANK) { return(cell); } if (policy == MissingCellPolicy.RETURN_BLANK_AS_NULL) { if (cell == null) { return(cell); } if (cell.CellType == NPOI.SS.UserModel.CellType.BLANK) { return(null); } return(cell); } if (policy == MissingCellPolicy.CREATE_NULL_AS_BLANK) { if (cell == null) { return(CreateCell((short)cellnum, NPOI.SS.UserModel.CellType.BLANK)); } return(cell); } throw new ArgumentException("Illegal policy " + policy + " (" + policy.id + ")"); }
/// <summary> /// Remove the Cell from this row. /// </summary> /// <param name="cell">The cell to Remove.</param> public void RemoveCell(NPOI.SS.UserModel.Cell cell) { if (cell == null) { throw new ArgumentException("cell must not be null"); } RemoveCell((HSSFCell)cell, true); }
public ExcelHelper OpenExcelFile(string filePath) { _hssfworkbook = new HSSFWorkbook(OpenFileStream(filePath)); _currentSheet = _hssfworkbook.GetSheetAt(0); _row = CurrentSheet.GetRow(0); _cell = _row.GetCell(0); return(this); }
/// <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> /// 把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()); } } }
/** * If cell Contains formula, it Evaluates the formula, * and saves the result of the formula. The cell * remains as a formula cell. * Else if cell does not contain formula, this method leaves * the cell UnChanged. * Note that the type of the formula result is returned, * so you know what kind of value is also stored with * the formula. * <pre> * int EvaluatedCellType = evaluator.EvaluateFormulaCell(cell); * </pre> * Be aware that your cell will hold both the formula, * and the result. If you want the cell Replaced with * the result of the formula, use {@link #EvaluateInCell(HSSFCell)} * @param cell The cell to Evaluate * @return The type of the formula result (the cell's type remains as NPOI.SS.UserModel.CellType.FORMULA however) */ public NPOI.SS.UserModel.CellType EvaluateFormulaCell(NPOI.SS.UserModel.Cell cell) { if (cell == null || cell.CellType != NPOI.SS.UserModel.CellType.FORMULA) { return(NPOI.SS.UserModel.CellType.Unknown); } NPOI.SS.UserModel.CellValue cv = EvaluateFormulaCellValue(cell); // cell remains a formula cell, but the cached value is changed SetCellValue(cell, cv); return(cv.CellType); }
/// <summary> /// used internally to refresh the "last cell" when the last cell is Removed. /// </summary> /// <param name="lastcell">The last cell index</param> /// <returns></returns> private short FindLastCell(int lastcell) { short cellnum = (short)(lastcell - 1); NPOI.SS.UserModel.Cell r = GetCell(cellnum); while (r == null && cellnum >= 0) { r = GetCell(--cellnum); } return(cellnum); }
/// <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); }
/** * If cell Contains formula, it Evaluates the formula, and * puts the formula result back into the cell, in place * of the old formula. * Else if cell does not contain formula, this method leaves * the cell UnChanged. * Note that the same instance of Cell is returned to * allow chained calls like: * <pre> * int EvaluatedCellType = evaluator.EvaluateInCell(cell).CellType; * </pre> * Be aware that your cell value will be Changed to hold the * result of the formula. If you simply want the formula * value computed for you, use {@link #EvaluateFormulaCell(HSSFCell)} * @param cell */ public NPOI.SS.UserModel.Cell EvaluateInCell(NPOI.SS.UserModel.Cell cell) { if (cell == null) { return(null); } if (cell.CellType == NPOI.SS.UserModel.CellType.FORMULA) { NPOI.SS.UserModel.CellValue cv = EvaluateFormulaCellValue(cell); SetCellValue(cell, cv); SetCellType(cell, cv); // cell will no longer be a formula cell } return(cell); }
/// <summary> /// used internally to refresh the "first cell" when the first cell is Removed. /// </summary> /// <param name="firstcell">The first cell index.</param> /// <returns></returns> private short FindFirstCell(int firstcell) { short cellnum = (short)(firstcell + 1); NPOI.SS.UserModel.Cell r = GetCell(cellnum); while (r == null && cellnum <= LastCellNum) { r = GetCell(++cellnum); } if (cellnum > LastCellNum) { return(-1); } return(cellnum); }
/// <summary> /// 右对齐数字格式 /// </summary> /// <param name="cell"></param> /// <returns></returns> public static CellStyle GetRightDataCellStyle(NPOI.SS.UserModel.Cell cell) { if (rightDataCellStyle == null) { CellStyle style = cell.Sheet.Workbook.CreateCellStyle(); style.BorderBottom = CellBorderType.THIN; style.BorderLeft = CellBorderType.THIN; style.BorderRight = CellBorderType.THIN; style.BorderTop = CellBorderType.THIN; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; NPOI.SS.UserModel.Font font = cell.Sheet.Workbook.CreateFont(); font.FontHeightInPoints = 9; style.SetFont(font); style.DataFormat = cell.Sheet.Workbook.CreateDataFormat().GetFormat("_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \" - \"??_ ;_ @_ "); rightDataCellStyle = style; } return(rightDataCellStyle); }
/// <summary> /// 居中对齐加粗格式 /// </summary> /// <param name="cell"></param> /// <returns></returns> public static CellStyle GetCenterBoldCellStyle(NPOI.SS.UserModel.Cell cell) { if (centerBoldCellStyle == null) { CellStyle style = cell.Sheet.Workbook.CreateCellStyle(); style.BorderBottom = CellBorderType.THIN; style.BorderLeft = CellBorderType.THIN; style.BorderRight = CellBorderType.THIN; style.BorderTop = CellBorderType.THIN; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; NPOI.SS.UserModel.Font font = cell.Sheet.Workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = (short)FontBoldWeight.BOLD; style.SetFont(font); centerBoldCellStyle = style; } return(centerBoldCellStyle); }
/// <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); }
private static void SetCellType(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: case NPOI.SS.UserModel.CellType.ERROR: case NPOI.SS.UserModel.CellType.NUMERIC: case NPOI.SS.UserModel.CellType.STRING: cell.SetCellType(cellType); return; case NPOI.SS.UserModel.CellType.BLANK: // never happens - blanks eventually get translated to zero break; case NPOI.SS.UserModel.CellType.FORMULA: // this will never happen, we have already evaluated the formula break; } throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")"); }
/// <summary> /// 根据Excel列类型获取列的值 /// </summary> /// <param name="cell">Excel列</param> /// <returns></returns> private static string GetCellValue(NPOI.SS.UserModel.Cell cell) { if (cell == null) { return(string.Empty); } switch (cell.CellType) { case CellType.BLANK: return(string.Empty); case CellType.BOOLEAN: return(cell.BooleanCellValue.ToString()); case CellType.ERROR: return(cell.ErrorCellValue.ToString()); case CellType.NUMERIC: case CellType.Unknown: default: return(cell.ToString()); //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number case CellType.STRING: return(cell.StringCellValue); case CellType.FORMULA: try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return(cell.ToString()); } catch { return(cell.NumericCellValue.ToString()); } } }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * Evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no Changes * are made. * This is a helpful wrapper around looping over all * cells, and calling EvaluateFormulaCell on each one. */ public static void EvaluateAllFormulaCells(HSSFWorkbook wb) { for (int i = 0; i < wb.NumberOfSheets; i++) { NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();) { HSSFRow r = (HSSFRow)rit.Current; //evaluator.SetCurrentRow(r); for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();) { NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current; if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA) { evaluator.EvaluateFormulaCell(c); } } } } }
/// <summary> /// 设置居中对齐加粗字符串格式 /// </summary> /// <param name="cell"></param> /// <returns></returns> public static NPOI.SS.UserModel.Cell SetCenterBoldStringCell(NPOI.SS.UserModel.Cell cell) { cell.SetCellType(CellType.STRING); cell.CellStyle = GetCenterBoldCellStyle(cell); return(cell); }
public ExcelHelper CreateCell(int cellnum) { _cell = _row.CreateCell(cellnum); return(this); }
public ExcelHelper GetCell(int col, int row) { _row = CurrentSheet.GetRow(row); _cell = _row.GetCell(col); return(this); }
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 GetCell(int col, int row) { _row = CurrentSheet.GetRow(row); _cell = _row.GetCell(col); return this; }
public ExcelHelper CreateCell(int cellnum) { _cell = _row.CreateCell(cellnum); return this; }
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); } }
public ExcelHelper OpenExcelFile(string filePath) { _hssfworkbook = new HSSFWorkbook(OpenFileStream(filePath)); _currentSheet = _hssfworkbook.GetSheetAt(0); _row = CurrentSheet.GetRow(0); _cell = _row.GetCell(0); return this; }
/// <summary> /// 设置右对齐数字格式 /// </summary> /// <param name="cell"></param> /// <returns></returns> public static NPOI.SS.UserModel.Cell SetRightDataCell(NPOI.SS.UserModel.Cell cell) { cell.SetCellType(CellType.NUMERIC); cell.CellStyle = GetRightDataCellStyle(cell); return(cell); }
/// <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); }