/// <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);
}
