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