/// <summary>
/// 导出
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream Export(this DataTable table)
{
var ms = new MemoryStream();
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet();
IDataFormat dataformat = workbook.CreateDataFormat();
ICellStyle style1 = workbook.CreateCellStyle();
style1.DataFormat = dataformat.GetFormat("text");
var headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
}
//If Caption not set, returns the ColumnName value
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
var columnValue = row[column].ToString();
if (column.DataType == typeof(Enum) ||
column.DataType.BaseType == typeof(Enum))
{
columnValue = column.DataType.GetDescription((int)row[column]);
}
var cell = dataRow.CreateCell(column.Ordinal);
cell.CellStyle = style1;
cell.SetCellValue(columnValue);
}
rowIndex++;
}
workbook.Write(ms);
var returnStream = new System.IO.MemoryStream(ms.ToArray());
return(returnStream);
}
private ICellStyle GetDateRowStyle(XSSFWorkbook hssfworkbook)
{
if (dateRowStyle == null)
{
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 8;
font.FontName = "Calibri";
dateRowStyle = hssfworkbook.CreateCellStyle();
dateRowStyle.SetFont(font);
dateRowStyle.BorderTop = BorderStyle.Thin;
dateRowStyle.BorderRight = BorderStyle.Thin;
dateRowStyle.BorderBottom = BorderStyle.Thin;
dateRowStyle.BorderLeft = BorderStyle.Thin;
dateRowStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("dd/mm/yyyy");
}
return(dateRowStyle);
}
private ICellStyle GetNumericRowStyle(XSSFWorkbook hssfworkbook)
{
if (numericRowStyle == null)
{
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 8;
font.FontName = "Calibri";
numericRowStyle = hssfworkbook.CreateCellStyle();
numericRowStyle.SetFont(font);
numericRowStyle.BorderTop = BorderStyle.Thin;
numericRowStyle.BorderRight = BorderStyle.Thin;
numericRowStyle.BorderBottom = BorderStyle.Thin;
numericRowStyle.BorderLeft = BorderStyle.Thin;
numericRowStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("#,#0.00");
}
return(numericRowStyle);
}
public ICellStyle total(XSSFWorkbook workbook)
{
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Medium;
cellStyle.BorderTop = BorderStyle.Medium;
cellStyle.BorderRight = BorderStyle.Medium;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.SetFont(font);
cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat(" #,##");
cellStyle.FillForegroundColor = IndexedColors.Gold.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
return(cellStyle);
}
public override void Test49928()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("49928.xlsx");
doTest49928Core(wb);
// an attempt to register an existing format returns its index
int poundFmtIdx = wb.GetSheetAt(0).GetRow(0).GetCell(0).CellStyle.DataFormat;
Assert.AreEqual(poundFmtIdx, wb.GetStylesSource().PutNumberFormat(poundFmt));
// now create a custom format with Pound (\u00a3)
IDataFormat dataFormat = wb.CreateDataFormat();
short customFmtIdx = dataFormat.GetFormat("\u00a3##.00[Yellow]");
Assert.IsTrue(customFmtIdx > BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
Assert.AreEqual("\u00a3##.00[Yellow]", dataFormat.GetFormat(customFmtIdx));
}
/// <summary>
/// 加载Excel模板样式(xlsx)
/// </summary>
/// <param name="file"></param>
private static void LoadExcelStyleForXLSX(string file)
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet s = xssfworkbook.GetSheetAt(0);
for (int i = s.FirstRowNum; i <= s.LastRowNum; i++)
{
ICell c0 = s.GetRow(i).GetCell(0);
ICell c1 = s.GetRow(i).GetCell(1);
CellStyles.Add(c0.StringCellValue, c1.CellStyle);
if (c0.StringCellValue == "DateTime")
{
IDataFormat format = xssfworkbook.CreateDataFormat();
CellStyles["DateTime"].DataFormat = format.GetFormat(c1.StringCellValue);
}
}
}
}
/// <summary>
/// Last Update: 31/03/2015 By Usman Akram
/// This Function will Convert the Format to Date & Time with Time Specified Format 12 hour or 24 hour
/// </summary>
/// <param name="workbook">Reference to Current WorkBook</param>
/// <param name="TimeFormat24Hour">True = 24 Hour Format e.g. 14:30 & False = 12 Hour Format e.g. 02:30 PM</param>
/// <returns></returns>
public static ICellStyle SetCellStyle_DateTime(ref XSSFWorkbook workbook, bool TimeFormat24Hour)
{
var DateTime_df = workbook.CreateDataFormat();
string _timeFormat = " hh:mm tt";
if (TimeFormat24Hour)
{
_timeFormat = " HH:mm";
}
short DateTime_dataFormat = DateTime_df.GetFormat("dd/MM/yyy" + _timeFormat);
var DateTime_style = workbook.CreateCellStyle();
DateTime_style.DataFormat = DateTime_dataFormat;
return(DateTime_style);
}
public static void ExportEntities <T>(IEnumerable <T> entities, string filename)
{
//Create Excel workbook
var wb = new XSSFWorkbook();
//Create Excel sheet
var sheet = wb.CreateSheet("Datos");
var dateFormat = wb.CreateDataFormat();
var style = wb.CreateCellStyle();
style.DataFormat = dateFormat.GetFormat("m/d/yy");
if (typeof(T) == typeof(Well))
{
ExportWells(entities.Cast <Well>().ToList(), sheet);
}
else if (typeof(T) == typeof(Measurement))
{
ExportMeasurements(entities.Cast <Measurement>().ToList(), sheet, style);
}
else if (typeof(T) == typeof(Precipitation))
{
ExportPrecipitations(entities.Cast <Precipitation>().ToList(), sheet, style);
}
else if (typeof(T) == typeof(FlnaAnalysis))
{
ExportFlnaAnalyses(entities.Cast <FlnaAnalysis>().ToList(), sheet, style);
}
else if (typeof(T) == typeof(WaterAnalysis))
{
ExportWaterAnalyses(entities.Cast <WaterAnalysis>().ToList(), sheet, style);
}
else if (typeof(T) == typeof(SoilAnalysis))
{
ExportSoilAnalyses(entities.Cast <SoilAnalysis>().ToList(), sheet, style);
}
using var stream = File.Open(filename, FileMode.Create, FileAccess.Write);
wb.Write(stream);
wb.Close();
}
public static byte[] GenerateXLSX(Dictionary <string, object[, ]> sheets)
{
using (MemoryStream stream = new MemoryStream())
{
IWorkbook wb = new XSSFWorkbook();
var cellStyles = new Dictionary <string, ICellStyle>();
var dataFormat = wb.CreateDataFormat();
var dateStyle = wb.CreateCellStyle();
dateStyle.DataFormat = dataFormat.GetFormat("yyyy-MMM-dd");
cellStyles.Add("date", dateStyle);
foreach (var sheet in sheets)
{
AddSheet(wb, sheet.Key, sheet.Value, cellStyles);
}
wb.Write(stream);
return(stream.ToArray());
}
}
public static void testc()
{
//1.创建EXCEL中的Workbook
IWorkbook myworkbook = new XSSFWorkbook();
//2.创建Workbook中的Sheet
ISheet mysheet = myworkbook.CreateSheet("sheet1");
mysheet.SetColumnWidth(0, 40 * 256);
//3.创建Row中的Cell并赋值
IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue("130925199662080044");
IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue("" + DateTime.Now + "");
//4.创建CellStyle与DataFormat并加载格式样式
IDataFormat dataformat = myworkbook.CreateDataFormat();
//【Tips】
// 1.使用@ 或 text 都可以
// 2.再也不用为身份证号发愁了
ICellStyle style0 = myworkbook.CreateCellStyle();
style0.DataFormat = dataformat.GetFormat("@");
ICellStyle style1 = myworkbook.CreateCellStyle();
style1.DataFormat = dataformat.GetFormat("text");
//5.将CellStyle应用于具体单元格
row0.GetCell(0).CellStyle = style0;
row1.GetCell(0).CellStyle = style1;
//6.保存
FileStream file = new FileStream(@"c:\myworkbook9.xlsx", FileMode.Create);
myworkbook.Write(file);
file.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
////1、使用response.TransmitFile传输400MB以上的文件，不使用缓存
//Response.ContentType = "application/x-zip-compressed";
//Response.AddHeader("Content-Disposition", "attachment;filename=text.txt");
//string fileName = Server.MapPath("text.txt");
//Response.TransmitFile(fileName);
//Response.Write("<script language=\"javascript\" type=\"text/javascript\">");
//Response.Write("alert(\"下载成功\");");
//Response.Write("window.location.href=\"C_SC.aspx\";");
//Response.Write("</script>");
//2、使用WriteFile方法下载文件
//string filename = Server.MapPath("text.txt");
//Response.ContentEncoding = Encoding.GetEncoding("gb2312");
//Response.WriteFile(filename);
//new DataTable
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("ID"));
table.Columns.Add(new DataColumn("Name"));
table.Columns.Add(new DataColumn("time", typeof(DateTime)));
table.Rows.Add(1, "name1", DateTime.Now);
table.Rows.Add(2, "name2", DateTime.Now);
//create excel
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.CreateSheet("report");
ISheet reportSheet = workbook.GetSheetAt(0);
//set columns value
int rowNum = 0;
IRow columnRow = reportSheet.CreateRow(rowNum++);
for (int i = 0; i < table.Columns.Count; i++)
{
ICell cell = columnRow.CreateCell(i);
cell.SetCellValue(table.Columns[i].ColumnName);
}
//set cells value
foreach (DataRow dataRow in table.Rows)
{
IRow row = reportSheet.CreateRow(rowNum++);
for (int i = 0; i < table.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
if (dataRow[i] is DateTime)
{
cell.SetCellValue((DateTime)dataRow[i]);
ICellStyle style = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy/MM/dd HH:mm:ss");
cell.CellStyle = style;
cell.SetCellType(CellType.Numeric);
}
cell.SetCellValue(dataRow[i].ToString());
}
}
string filename = "test.xlsx";
workbook.Write(Response.OutputStream);
workbook.Close();
Response.ContentType = "application/x-zip-compressed";
Response.AddHeader("Content-Disposition", $"attachment;filename={filename}");
Response.End();
//XSSFWorkbook workbook = new XSSFWorkbook();
//workbook.CreateSheet("sheet1");
//workbook.CreateSheet("sheet2");
//workbook.CreateSheet("sheet3");
//Stream outStream = Response.OutputStream;
//workbook.Write(outStream);
//outStream.Close();
//workbook.Close();
}
public void Main()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
//increase the width of Column A
sheet.SetColumnWidth(0, 5000);
//create the format instance
IDataFormat format = workbook.CreateDataFormat();
// Create a row and put some cells in it. Rows are 0 based.
ICell cell = sheet.CreateRow(0).CreateCell(0);
//number format with 2 digits after the decimal point - "1.20"
SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));
//RMB currency format with comma - "¥20,000"
ICell cell2 = sheet.CreateRow(1).CreateCell(0);
SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"));
//scentific number format - "3.15E+00"
ICell cell3 = sheet.CreateRow(2).CreateCell(0);
SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"));
//percent format, 2 digits after the decimal point - "99.33%"
ICell cell4 = sheet.CreateRow(3).CreateCell(0);
SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"));
//phone number format - "021-65881234"
ICell cell5 = sheet.CreateRow(4).CreateCell(0);
SetValueAndFormat(workbook, cell5, 02165881234, format.GetFormat("000-00000000"));
//Chinese capitalized character number - 壹贰叁 元
ICell cell6 = sheet.CreateRow(5).CreateCell(0);
SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元"));
//Chinese date string
ICell cell7 = sheet.CreateRow(6).CreateCell(0);
SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
cell7.SetCellValue(new DateTime(2004, 5, 6));
//Chinese date string
ICell cell8 = sheet.CreateRow(7).CreateCell(0);
SetValueAndFormat(workbook, cell8, new DateTime(2005, 11, 6), format.GetFormat("yyyy年m月d日"));
//formula value with datetime style
ICell cell9 = sheet.CreateRow(8).CreateCell(0);
cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
ICellStyle cellStyle9 = workbook.CreateCellStyle();
cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
cell9.CellStyle = cellStyle9;
//display current time
ICell cell10 = sheet.CreateRow(9).CreateCell(0);
SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"));
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// DataTable导出到FileStreamExcel文件
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strHeaderText"></param>
/// <param name="fs"></param>
public static void Export(DataTable dtSource, string strHeaderText, Stream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
workbook = null;
// fs.Flush();
// ms.Position = 0;
// workbook.Dispose();
// return fs;
// }
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream DatagridviewToExcel(DataGridView myDgv, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
try
{
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
}
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
}
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <param name="headerText">表头文本</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public static MemoryStream DataTableToExcel(DataTable data, string fileName, string sheetName, string headerText, bool isColumnWritten = true)
{
try
{
if (data == null || data.Rows.Count == 0)
{
headerText = "";
data = new DataTable();
data.Columns.Add(" 无数据 ");
//data.Rows.Add(" 无数据 ");
}
int maxRow = 0;
IDataFormat format = null;
IWorkbook workbook = null;
//根据后缀名判断execl的版本
if (fileName.ToString().ToLower().EndsWith(".xlsx")) //2007以上版本
{
workbook = new XSSFWorkbook();
maxRow = 1048576;
format = workbook.CreateDataFormat() as XSSFDataFormat;
}
else
{
workbook = new HSSFWorkbook();
maxRow = 65535;
format = workbook.CreateDataFormat() as HSSFDataFormat;
}
ISheet sheet = null;
if (workbook != null)
{
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
sheet = workbook.CreateSheet();
}
}
else
{
return(null);
}
//日期样式
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
int rowIndex = 0;
//标题行
if (!string.IsNullOrEmpty(headerText))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(headerText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//边框
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//边框颜色
headStyle.BottomBorderColor = 8;
headStyle.TopBorderColor = 8;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.IsBold = true;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, data.Columns.Count - 1));
rowIndex++;
}
//写入DataTable的列名
if (isColumnWritten)
{
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
rowIndex++;
}
//写入行内容
foreach (DataRow row in data.Rows)
{
if (rowIndex > maxRow)
{
rowIndex = 0;
sheet = workbook.CreateSheet();
}
if (rowIndex <= maxRow)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in data.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column]?.ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (double.TryParse(drValue, out result))
{
newCell.SetCellValue(drValue);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
newCell.SetCellValue(DateTime.TryParse(drValue, out dateV) ? drValue : "");
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
}
var dtResult = from a in data.AsEnumerable()
select a;
int colLength = 0;
//列宽自适应，只对英文和数字有效
for (int i = 0; i < data.Columns.Count; i++)
{
//sheet.AutoSizeColumn(i);//自适应 速度慢
//sheet.SetColumnWidth(i, 16 * 256 + 200); // 200为常量，这样即可控制列宽为16
try
{
colLength = dtResult.Where(p => p[data.Columns[i].ColumnName] != DBNull.Value)
.Select(p => System.Text.Encoding.Default.GetBytes(p.Field <string>(data.Columns[i].ColumnName)).Length).Max();
}
catch
{
}
if (colLength < 12)
{
colLength = 12;
}
if (colLength > 100)
{
colLength = 100;
}
colLength += 4;
sheet.SetColumnWidth(i, colLength * 256 + 200); // 200为常量
colLength = 0;
}
NPOIMemoryStream ms = new NPOIMemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
catch (Exception ex)
{
var a = ex.Message;
return(null);
}
}
/// <summary>
/// 将DataTable转换为Excel并保存
/// </summary>
/// <param name="dt">目标数据表</param>
/// <param name="excelFullPath">目标excel文件路径</param>
/// <param name="sheetName">工作表名称</param>
public static void DataTableToExcel(DataTable dt, string excelFullPath, string sheetName = "Sheet1")
{
if (!dt.HasDataRow())
{
throw new BizException("目标数据不能为空");
}
if (excelFullPath.IsNullOrEmptyWhiteSpace())
{
throw new BizException("目标文件路径不能为空");
}
var workbook = new XSSFWorkbook();
try
{
var sheet = workbook.CreateSheet(sheetName);
var headercellStyle = workbook.CreateCellStyle();
headercellStyle.BorderBottom = BorderStyle.Thin;
headercellStyle.BorderLeft = BorderStyle.Thin;
headercellStyle.BorderRight = BorderStyle.Thin;
headercellStyle.BorderTop = BorderStyle.Thin;
headercellStyle.Alignment = HorizontalAlignment.Center;
//字体
var headerfont = workbook.CreateFont();
//字体大小
headerfont.FontHeightInPoints = 10;
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headercellStyle.SetFont(headerfont);
//用column name 作为列名
var colIndex = 0;
var headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(colIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = headercellStyle;
colIndex++;
}
var cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定format为"@"表示一率当成text來看
var fmt = workbook.CreateDataFormat();
cellStyle.DataFormat = fmt.GetFormat("@");
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
var cellfont = workbook.CreateFont();
//字体大小
cellfont.FontHeightInPoints = 10;
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
var rowIndex = 1;
var cellIndex = 0;
foreach (DataRow rowitem in dt.Rows)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn colitem in dt.Columns)
{
var cell = dataRow.CreateCell(cellIndex);
cell.SetCellValue(rowitem[colitem].ToString());
cell.CellStyle = cellStyle;
cellIndex++;
}
cellIndex = 0;
rowIndex++;
}
//自适应列宽度
for (var i = 0; i < colIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
var file = new FileStream(excelFullPath, FileMode.OpenOrCreate);
workbook.Write(file);
file.Close();
}
finally
{
workbook.Close();
}
}
public static bool ExportToExcel(DataTable dataTable, string pathToSave)
{
try
{
if (dataTable == null || string.IsNullOrEmpty(pathToSave))
{
return(false);
}
var workbook = new XSSFWorkbook();
var sheet = (XSSFSheet)workbook.CreateSheet("Result");
var row = sheet.CreateRow(0);
foreach (DataColumn column in dataTable.Columns)
{
var cell = row.CreateCell(dataTable.Columns.IndexOf(column));
cell.SetCellValue(column.ColumnName);
}
for (var i = 0; i < dataTable.Rows.Count; i++)
{
var tableRow = dataTable.Rows[i];
var currentRow = sheet.CreateRow(i + 1);
var values = tableRow.ItemArray.ToList();
foreach (var item in values)
{
var currentCell = currentRow.CreateCell(values.IndexOf(item));
DateTime dateValue;
double doubleValue;
int intValue;
if (DateTime.TryParse(item.ToString(), out dateValue))
{
if (_cellStyleDateTime == null)
{
var format = workbook.CreateDataFormat();
_cellStyleDateTime = workbook.CreateCellStyle();
_cellStyleDateTime.DataFormat = format.GetFormat("dd/MM/yyyy");
}
currentCell.SetCellValue(dateValue);
currentCell.CellStyle = _cellStyleDateTime;
}
else if (double.TryParse(item.ToString(), out doubleValue))
{
currentCell.SetCellValue(doubleValue);
}
else if (int.TryParse(item.ToString(), out intValue))
{
currentCell.SetCellValue(intValue);
}
else if (item is byte[])
{
InsertImage(workbook, sheet, (byte[])item, i + 1, values.IndexOf(item));
}
else
{
currentCell.SetCellValue(item.ToString());
}
}
}
using (var fs = new FileStream(pathToSave, FileMode.Create, FileAccess.Write))
workbook.Write(fs);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Export to Excel", MessageBoxButton.OK);
return(false);
}
}
/// <summary>
/// 组装workbook.
/// </summary>
/// <param name="dt">dataTable资源</param>
/// <param name="columnHeader">列头</param>
/// <returns></returns>
public static XSSFWorkbook BuildWorkbook(DataTable dt, string strColumnHeader = "")
{
var workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet1" : dt.TableName);
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
var arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (var i = 0; i < dt.Rows.Count; i++)
{
for (var j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;//行号
foreach (DataRow row in dt.Rows)
{
#region 表头 列头
if (rowIndex == 1048576 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
if (strColumnHeader == "")
{
IRow row0 = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row0.CreateCell(i, CellType.String);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
else
{
List <ColumnJson> objJsonList = Newtonsoft.Json.JsonConvert.DeserializeObject <List <ColumnJson> >(strColumnHeader);
#region 设置合并行
foreach (ColumnJson item in objJsonList)
{
if (item.list[1] > rowIndex)
{
rowIndex = item.list[1];
}
CellRangeAddress cell = new CellRangeAddress(item.list[0], item.list[1], item.list[2], item.list[3]);
sheet.AddMergedRegion(cell);
}
#endregion
#region 对合并行赋值
// 定义行样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // 左右居中
headStyle.VerticalAlignment = VerticalAlignment.Center; // 上下居中
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i <= rowIndex; i++)
{
IRow headerRow = sheet.CreateRow(i);
foreach (ColumnJson item in objJsonList)
{
if (item.list[0] == i)
{
headerRow.CreateCell(item.list[2]).SetCellValue(item.Name);
headerRow.GetCell(item.list[2]).CellStyle = headStyle;
}
}
}
#endregion
}
rowIndex++;
}
#endregion
#region 内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV.ToString("yyyy-MM-dd hh:mm:ss"));
//newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
return(workbook);
}
public void OutExcelData()
{
string ExportFileName = "EmpData.xlsx"; string ExportFileTitle = "Data"; Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
/// 進行產生Excel檔案流程
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = edModel.listEmployeeColumn;
/// 建立標題列
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]);
}
/// 讀取資料庫資料
List <listEmployeeDetail> ListEmpData = new List <listEmployeeDetail>();
ListEmpData = edModel.ReListEmployeeDetail();
if (ListEmpData != null && ListEmpData.Count > 0)
{
for (int i = 0; i < ListEmpData.Count; i++)
{
listEmployeeDetail item = ListEmpData[i];
List <string> list = new List <string>();
list.Add(item.lEmpIndex.ToString());
list.Add(item.lEmpName.ToString());
list.Add(item.lEmpSex.ToString());
list.Add(item.lEmpEmail.ToString());
list.Add(item.lEmpAddress.ToString());
list.Add(item.lEmpMobile.ToString());
list.Add(item.lEmpPhone.ToString());
list.Add(item.lEmpNotation.ToString());
list.Add(item.lEmpRemark.ToString());
list.Add(item.lEmpStatus.ToString());
list.Add(item.lEmpJoinDate.ToString());
list.Add(item.lEmpLeaveDate.ToString());
IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 30;
for (int b = 0; b < list.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(list[b]);
}
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
// ----------------------------------------------------------------------------------------------
// 釋放記憶體參數
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush(); Response.End();
}
private void SetCellValueFromProperty(object item, PropertyInfo property, ICell valueCell)
{
var propertyName = property.Name;
if (propertyName == "PatientId")
{
return;
}
if (propertyName == "SampleId")
{
return;
}
if (propertyName.Contains("Id") && !propertyName.Contains("Ids") && !propertyName.StartsWith("Id"))
{
var navigationPropertyName = propertyName.Replace("Id", "");
var navigationProperty = item.GetType().GetProperty(navigationPropertyName);
var nameValue = ExtensionMethods.GetValueFromProperty(item, navigationPropertyName + ".Name");
if (nameValue == "")
{
nameValue = ExtensionMethods.GetValueFromProperty(item, navigationPropertyName + ".CategoryName");
}
valueCell.SetCellType(CellType.String);
valueCell.SetCellValue(nameValue);
}
else
{
TypeCode typeCode;
bool isNullableValue = property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>);
if (isNullableValue)
{
Type propertyType = Nullable.GetUnderlyingType(property.PropertyType);
typeCode = Type.GetTypeCode(propertyType);
}
else
{
typeCode = Type.GetTypeCode(property.PropertyType);
}
switch (typeCode)
{
case TypeCode.Decimal:
IDataFormat format = _outputWorkbook.CreateDataFormat();
var propertyValue = property.GetValue(item);
var fromatted = String.Format("{0:0.00}", propertyValue);
SetNumericValueAndFormat(valueCell, Convert.ToDouble(fromatted), format.GetFormat("0.00"));
break;
case TypeCode.Int32:
var integerPropertyValue = Convert.ToInt32(property.GetValue(item));
valueCell.SetCellValue(integerPropertyValue);
valueCell.SetCellType(CellType.Numeric);
break;
case TypeCode.String:
var stringPropertyValue = property.GetValue(item)?.ToString();
valueCell.SetCellType(CellType.String);
valueCell.SetCellValue(stringPropertyValue);
break;
case TypeCode.DateTime:
var datePropertyValue = Convert.ToDateTime(property.GetValue(item));
valueCell.SetCellValue(datePropertyValue.ToString("dd/MM/yyyy"));
break;
default:
var propVal = property.GetValue(item)?.ToString();
valueCell.SetCellType(CellType.String);
valueCell.SetCellValue(propVal);
break;
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportToMemorySteram(DataTable dtSource, string strHeaderText)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "Lee"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "宋轶"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
var dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
var format = workbook.CreateDataFormat() as HSSFDataFormat;
if (dateStyle != null)
{
if (format != null)
{
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
}
//取得列宽
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
}
for (var i = 0; i < dtSource.Rows.Count; i++)
{
for (var j = 0; j < dtSource.Columns.Count; j++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
var rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
if (sheet != null)
{
var headerRow = sheet.CreateRow(0) as XSSFRow;
if (headerRow != null)
{
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
if (headStyle != null)
{
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont() as XSSFFont;
if (font != null)
{
font.FontHeightInPoints = 20;
font.Boldweight = 700;
font.IsBold = true;
headStyle.SetFont(font);
}
headerRow.GetCell(0).CellStyle = headStyle;
}
}
}
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
if (sheet != null)
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
if (sheet != null)
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
if (headStyle != null)
{
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont() as XSSFFont;
if (font != null)
{
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
}
foreach (DataColumn column in dtSource.Columns)
{
if (headerRow != null)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
if (sheet != null)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
#endregion
rowIndex++;
}
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public IDataFormat CreateDataFormat()
{
return(XssfWorkbook.CreateDataFormat());
}
public static void WriteExcel(DataTable dt, string fileName, List <string> headers = null)
{
if (string.IsNullOrEmpty(fileName))
{
fileName = "temp";
}
IWorkbook book = new XSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
IRow rowTitle = sheet.CreateRow(0);
#region 设置列头
var columnNumber = 0;
if (headers != null)
{
columnNumber = headers.Count;
for (int i = 0; i < headers.Count; i++)
{
rowTitle.CreateCell(i).SetCellValue(headers[i]);
sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
}
}
else
{
columnNumber = dt.Columns.Count;
for (int i = 0; i < dt.Columns.Count; i++)
{
var val = dt.Columns[i] == null ? "" : dt.Columns[i].ToString();
rowTitle.CreateCell(i).SetCellValue(val);
sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
}
}
#endregion
var _doubleCellStyle = book.CreateCellStyle();
_doubleCellStyle.DataFormat = book.CreateDataFormat().GetFormat("#,##0.00");
var VerificationResultColumnCount = -1;
var CommentsColumnCount = -1;
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
var dataRow = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
var cellValue = dataRow[j];
string value = string.Empty;
if (cellValue is decimal)
{
var temp = Convert.ToDouble(dataRow[j]);
// value = temp.ToString("#0.##");
var cell = row.CreateCell(j);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(temp);
//transfer
row.Cells[j].CellStyle = _doubleCellStyle;
}
else if (cellValue is DateTime)
{
value = dataRow[j] == null ? "" : Convert.ToDateTime(dataRow[j]).ToString("yyyy-MM-dd");
row.CreateCell(j).SetCellValue(value);
}
else
{
value = dataRow[j] == null ? "" : dataRow[j].ToString();
row.CreateCell(j).SetCellValue(value);
var cs = book.CreateCellStyle();
if (dt.Columns[j].ColumnName == "VerificationResult")
{
cs.WrapText = true;
row.Cells[j].CellStyle = cs;
VerificationResultColumnCount = j;
}
if (dt.Columns[j].ColumnName == "Comments")
{
CommentsColumnCount = j;
}
}
}
}
#region 设置列宽
for (int i = 0; i < columnNumber; i++)
{
sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
}
if (VerificationResultColumnCount > -1)
{
sheet.SetColumnWidth(VerificationResultColumnCount, 55 * 256);
}
if (CommentsColumnCount > -1)
{
sheet.SetColumnWidth(CommentsColumnCount, 55 * 256);
}
#endregion
string customFileName = fileName + ".xlsx";//客户端保存的文件名
using (FileStream dumpFile = new FileStream(customFileName, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
book.Write(dumpFile);
book.Close();
};
}
public void CreateExcel()
{
IWorkbook workbook = new XSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet(SheetName);
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var RowIter = 0;
var ColIter = 0;
// Header Line - Food
var row = sheet.CreateRow(RowIter);
ColIter = 0;
row.CreateCell(ColIter).SetCellValue("Food");
RowIter++;
// Data Lines - Food
row = sheet.CreateRow(RowIter);
ColIter = 0;
dicListDate.TryGetValue("Food", out List <string> ListDate);
foreach (var val in ListDate)
{
DateTime t = ParseStringToDateTime(val);
NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter);
cell.SetCellValue(t);
cell.CellStyle = cellStyle;
ColIter++;
}
RowIter++;
row = sheet.CreateRow(RowIter);
ColIter = 0;
dicListFpi.TryGetValue("Food", out List <double> ListFpi);
foreach (var val in ListFpi)
{
row.CreateCell(ColIter).SetCellValue(val);
ColIter++;
}
RowIter++;
foreach (KeyValuePair <string, List <double> > entry
in dicListFpi.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value))
{
if (entry.Key == "Food")
{
continue;
}
// Header Line - Except Food
row = sheet.CreateRow(RowIter);
ColIter = 0;
row.CreateCell(ColIter).SetCellValue(entry.Key);
RowIter++;
// Data Lines - Except Food
row = sheet.CreateRow(RowIter);
ColIter = 0;
foreach (var val in entry.Value)
{
DateTime t = ParseStringToDateTime(dicListDate[entry.Key][ColIter]);
NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter);
cell.SetCellValue(t);
cell.CellStyle = cellStyle;
//row.CreateCell(ColIter).SetCellValue(t);
ColIter++;
}
RowIter++;
row = sheet.CreateRow(RowIter);
ColIter = 0;
foreach (var val in entry.Value)
{
row.CreateCell(ColIter).SetCellValue(val);
ColIter++;
}
RowIter++;
}
// Save Workbook
FileInfo FI = new FileInfo(FilePath);
FI.Directory.Create(); // If the directory already exists, this method does nothing.
FileStream file = new FileStream(FilePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
catch
{
throw;
}
finally
{
workbook.Close();
}
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream RenderToExcel(DataTable table, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">
/// 源DataTable
/// </param>
/// <param name="strHeaderText">
/// 表头文本
/// </param>
/// <returns>
/// The export.
/// </returns>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xls文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
//#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
// ms.Position = 0;
// workbook.Dispose();
return(ms);
}
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, XSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
ICell newCell = null;
System.Type rowType = drSource[cellIndex].GetType();
string drValue = drSource[cellIndex].ToString().Trim();
switch (rowType.ToString())
{
case "System.String": //字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
ICellStyle cellStyle = excelWorkBook.CreateCellStyle();
IDataFormat format = excelWorkBook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
public static bool DataSetToExcel(DataSet ds, string Path)
{
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
////自定义表头
string name = ds.Tables[i].TableName;
//for (var j = 0; j < dataList[name].ToArray().Count(); j++)
//{
// headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]);
// headerRow.GetCell(j).CellStyle = headStyle;
//}
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
public void WriteList_To_Excel(int rowAvailableCell, int startingCol, int startingReadColumn, int lastReadColumn, List <List <object> > data, int type)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
var newDataFormat = workbook.CreateDataFormat();
for (int rowCounter = 0; rowCounter < data.Count; rowCounter++)
{
for (int columnCounter = 0, dataStartColumn = startingReadColumn; dataStartColumn <= lastReadColumn; columnCounter++, dataStartColumn++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
if (data[rowCounter][dataStartColumn] != null)
{
switch (type)
{
case 0:
double value;
if (Double.TryParse(data[rowCounter][dataStartColumn].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
}
cell.CellStyle = cellStyle;
break;
case 1:
try
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
cell.SetCellValue("");
cell.CellStyle = cellStyle;
}
break;
case 2:
int dateValue;
DateTime dateTime;
cell.CellStyle = cellStyle;
cell.CellStyle.DataFormat = newDataFormat.GetFormat(defaultDateFormat);
if (int.TryParse(data[rowCounter][dataStartColumn].ToString(), out dateValue))
{
cell.SetCellValue(dateValue);
}
else if (DateTime.TryParse(data[rowCounter][dataStartColumn].ToString(), /*CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.None*/ out dateTime))
{
cell.SetCellValue(dateTime);
}
else
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
}
break;
}
}
}
catch (Exception ex)
{
logger.logException(ex);
}
}
}
}
