最近在做一个项目，涉及到将数据导出到excel，因此花了一天多时间，从网上查询了一些相关方法：下面做一下总结：
下面这个项目里面的步骤是本人已经成功应用到项目中的。
第一步骤：将NPOI DLL文件引用到项目中：
然后在项目引中会出现
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
第二步：打开保存文件夹
SaveFileDialog sfd = new SaveFileDialog();
//设置文件类型
sfd.Filter = "Excel文件（*.xls）|*.xls";
//设置默认文件类型显示顺序
sfd.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
sfd.RestoreDirectory = true;
//设置默认的文件名
DateTime dataNow = DateTime.Now;
string fileName = "XXXXXx" + dataNow.ToString("yyyy-MM-dd-HH-mm-ss");
sfd.FileName = fileName;
string localFilePath = null;
//点了保存按钮进入
if (sfd.ShowDialog() == DialogResult.OK)
{
localFilePath = sfd.FileName.ToString(); //获得文件路径
//string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
}
else
{
return;
}
第三步：
IWorkbook wb = new HSSFWorkbook();//建立一个excel文件
//创建一个工作簿
ISheet sh = wb.CreateSheet(captionName);
#region 表头(第1行)
//合并单元格
sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, propDictionary.Count - 1));
//创建第1行
IRow row0 = sh.CreateRow(0);
//设置第1行高度
row0.Height = 20 * 20;
//创建第1行第1列
ICell icell1top0 = row0.CreateCell(0);
//设置第1行第1列格式
icell1top0.CellStyle = Getcellstyle(wb, "head");
//设置第1行第1列内容
icell1top0.SetCellValue(captionName);
#endregion
#region 抬头(第2行)
//创建第2行
IRow row1 = sh.CreateRow(1);
//设置高度
row1.Height = 20 * 20;
//columnt_index是列的索引
int columnt_index = 0;
ICellStyle style = Getcellstyle(wb, "");
foreach (var itemtmp in propDictionary)
{
//获取T的字段名称
string name = itemtmp.Key.ToString();
//创建第2行的第columnt_index列
ICell icell1top = row1.CreateCell(columnt_index);
//设置第2行的第columnt_index列的格式
icell1top.CellStyle = style;
//设置第2行的第columnt_index列的内容
icell1top.SetCellValue(name);
//设置第2行的第columnt_index列的宽度
sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
columnt_index++;
}
#endregion
#region 这里是data具体内容
//创建第3行
int rownums = 2;
foreach (var item in resultToXLS.Items)
{
IRow row_zs = sh.CreateRow(rownums);
//创建第1列、格式、字段值
ICell icell1top = row_zs.CreateCell(0);
icell1top.CellStyle = style;
icell1top.SetCellValue(item.ID.ToString());
ICell icell1top1 = row_zs.CreateCell(1);
icell1top1.CellStyle = style;
icell1top1.SetCellValue(item.PlateNum);
ICell icell1top2 = row_zs.CreateCell(2);
icell1top2.CellStyle = style;
icell1top2.SetCellValue(item.PlateColor);
ICell icell1top3 = row_zs.CreateCell(3);
icell1top3.CellStyle = style;
icell1top3.SetCellValue(item.PassTime.ToString("yyyy-MM-dd HH:mm:ss"));
ICell icell1top4 = row_zs.CreateCell(4);
icell1top4.CellStyle = style;
icell1top4.SetCellValue(item.Result);
ICell icell1top5 = row_zs.CreateCell(5);
icell1top5.CellStyle = style;
icell1top5.SetCellValue(item.CO.ToString());
ICell icell1top6 = row_zs.CreateCell(6);
icell1top6.CellStyle = style;
icell1top6.SetCellValue(item.CO2.ToString());
ICell icell1top7 = row_zs.CreateCell(7);
icell1top7.CellStyle = style;
icell1top7.SetCellValue(item.NO.ToString());
ICell icell1top8 = row_zs.CreateCell(8);
icell1top8.CellStyle = style;
icell1top8.SetCellValue(item.HC.ToString());
ICell icell1top9 = row_zs.CreateCell(9);
icell1top9.CellStyle = style;
icell1top9.SetCellValue(item.Opacity.ToString());
ICell icell1top10 = row_zs.CreateCell(10);
icell1top10.CellStyle = style;
icell1top10.SetCellValue(item.K.ToString());
ICell icell1top11 = row_zs.CreateCell(11);
icell1top11.CellStyle = style;
icell1top11.SetCellValue(item.SF.ToString());
ICell icell1top12 = row_zs.CreateCell(12);
icell1top12.CellStyle = style;
icell1top12.SetCellValue(item.Speed.ToString());
ICell icell1top13 = row_zs.CreateCell(13);
icell1top13.CellStyle = style;
icell1top13.SetCellValue(item.Acc.ToString());
ICell icell1top14 = row_zs.CreateCell(14);
icell1top14.CellStyle = style;
icell1top14.SetCellValue(item.Temperature.ToString());
ICell icell1top15 = row_zs.CreateCell(15);
icell1top15.CellStyle = style;
icell1top15.SetCellValue(item.WindSpeed.ToString());
ICell icell1top16 = row_zs.CreateCell(16);
icell1top16.CellStyle = style;
icell1top16.SetCellValue(item.WindDirection.ToString());
ICell icell1top17 = row_zs.CreateCell(17);
icell1top17.CellStyle = style;
icell1top17.SetCellValue(item.Pressure.ToString());
ICell icell1top18 = row_zs.CreateCell(18);
icell1top18.CellStyle = style;
icell1top18.SetCellValue(item.EntireBelieve.ToString());
ICell icell1top19 = row_zs.CreateCell(19);
icell1top19.CellStyle = style;
icell1top19.SetCellValue(item.Humidity.ToString());
ICell icell1top20 = row_zs.CreateCell(20);
icell1top20.CellStyle = style;
icell1top20.SetCellValue(item.IsUploaded.ToString());
rownums++;
}
#endregion
//输出内容
if (string.IsNullOrEmpty(localFilePath))
{
return;
}
using (FileStream stm = File.OpenWrite(@localFilePath))
{
wb.Write(stm);
MessageBox.Show("导出Excle完成！");
}
第四步：
/// 格式设置
/// </summary>
static ICellStyle Getcellstyle(IWorkbook wb, string type)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义字体
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进
cellStyle.Indention = 0;
switch (type)
{
case "head":
cellStyle.SetFont(font);
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
break;
default:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}
以上就是成功应用的步骤。
private void Export()
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = "export";
saveDialog.DefaultExt = "xlsx";
saveDialog.AddExtension = true;
saveDialog.RestoreDirectory = true;
DialogResult result = saveDialog.ShowDialog();
if(result==DialogResult.OK)
{
DateTime start = dateEditStart.DateTime;
DateTime end = dateEditEnd.DateTime.AddDays(1).AddSeconds(-1);
List<StaticTesting> dataList = staticTestingRepository.GetByTime(start, end);
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet");
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("11111");
row0.CreateCell(1).SetCellValue("222222");
row0.CreateCell(2).SetCellValue("333333");
for (int r = 0; r < dataList.Count; r++)
{
IRow row = sheet.CreateRow(r+1);
row.CreateCell(0).SetCellValue(dataList[r].111);
row.CreateCell(1).SetCellValue(dataList[r].222);
row.CreateCell(2).SetCellValue(dataList[r].333);
}
using (FileStream fs = File.OpenWrite(saveDialog.FileName))
{
workbook.Write(fs);
fs.Flush();
fs.Close();
};
}
}
下面是我统计一些较为有用的方法
1、使用NPOI处理大数据量EXCEl2007
excel2003只能存储65536行数据，而excel2007能存储100多万行数据。所以针对日后的数据的一个可预见性增长，所以采用excel2007来处理数据，但是C#默认的excel
的处理方式只能处理excel2003，所以为了处理大数据的excel2007，我们使用NPOI来进行处理
需要引入相应的dll文件，具体引入方法就不再多说，具体的实现方式见下方代码。
*生成导出EXCEl文件对话框
*同时将导出文件类型定义为Excel
*保存时只需指明对应的文件名即可
*/
SaveFileDialog sdfexport = new SaveFileDialog();
sdfexport.Filter = "Excel文件|*.xlsx";
if (sdfexport.ShowDialog() == DialogResult.No)
{
return;
}
string filename = sdfexport.FileName;
//创建Excel2007工作簿
IWorkbook book = new XSSFWorkbook();
//创建Excel2007工作表
ISheet sheet = book.CreateSheet("standard_template");
//创建Excel行
IRow row = sheet.CreateRow(0);
//给单元格赋值
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("大区(区域)");
row.CreateCell(2).SetCellValue("省(简)");
row.CreateCell(3).SetCellValue("说明");
/*
* 将Excel文件写入相应的Excel文件中
*/
FileStream fs = File.Create(filename);
book.Write(fs);
fs.Close();
2、NPOI导入导出EXCEL通用类，供参考，可直接使用在WinForm项目中
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using Zwj.TEMS.Common;
namespace TEMS.Service
{
public static class ExcelHelperForCs
{
#region 私有方法
/// <summary>
/// 获取要保存的文件名称（含完整路径）
/// </summary>
/// <returns></returns>
private static string GetSaveFilePath()
{
SaveFileDialog saveFileDig = new SaveFileDialog();
saveFileDig.Filter = "Excel Office97-2003(*.xls)|.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
saveFileDig.FilterIndex = 0;
saveFileDig.OverwritePrompt = true;
saveFileDig.InitialDirectory = Common.DesktopDirectory;
string filePath = null;
if (saveFileDig.ShowDialog() == DialogResult.OK)
{
filePath = saveFileDig.FileName;
}
return filePath;
}
/// <summary>
/// 判断是否为兼容模式
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static bool GetIsCompatible(string filePath)
{
return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
}
/// <summary>
/// 创建工作薄
/// </summary>
/// <param name="isCompatible"></param>
/// <returns></returns>
private static IWorkbook CreateWorkbook(bool isCompatible)
{
if (isCompatible)
{
return new HSSFWorkbook();
}
else
{
return new XSSFWorkbook();
}
}
/// <summary>
/// 创建工作薄(依据文件流)
/// </summary>
/// <param name="isCompatible"></param>
/// <param name="stream"></param>
/// <returns></returns>
private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
{
if (isCompatible)
{
return new HSSFWorkbook(stream);
}
else
{
return new XSSFWorkbook(stream);
}
}
/// <summary>
/// 创建表格头单元格
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
return style;
}
/// <summary>
/// 从工作表中生成DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i + 1;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
{
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
}
return table;
}
#endregion
#region 公共导出方法
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <returns>Excel工作表</returns>
public static string ExportToExcel(DataSet sourceDs, string filePath = null)
{
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = GetIsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
ICellStyle cellStyle = GetCellStyle(workbook);
for (int i = 0; i < sourceDs.Tables.Count; i++)
{
DataTable table = sourceDs.Tables[i];
string sheetName = "result" + i.ToString();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = cellStyle;
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
}
rowIndex++;
}
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
workbook = null;
return filePath;
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <returns>Excel工作表</returns>
public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null)
{
if (sourceTable.Rows.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = GetIsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = cellStyle;
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue((row[column]??"").ToString());
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// 由List导出Excel
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="data">在导出的List</param>
/// <param name="sheetName">sheet名称</param>
/// <returns></returns>
public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class
{
if (data.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = GetIsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < headerNameList.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(headerNameList[i].Value);
cell.CellStyle = cellStyle;
}
Type t = typeof(T);
int rowIndex = 1;
foreach (T item in data)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < headerNameList.Count; n++)
{
object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null);
dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// 由DataGridView导出
/// </summary>
/// <param name="grid"></param>
/// <param name="sheetName"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
{
if (grid.Rows.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = GetIsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < grid.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(grid.Columns[i].Name);
cell.CellStyle = cellStyle;
}
int rowIndex = 1;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < grid.Columns.Count; n++)
{
dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
}
rowIndex++;
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
#endregion
#region 公共导入方法
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataTable</returns>
public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
if (int.TryParse(sheetName, out sheetIndex))
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = GetIsCompatible(excelFilePath);
return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible);
}
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataSet</returns>
public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
{
DataSet ds = new DataSet();
IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = GetIsCompatible(excelFilePath);
return ImportFromExcel(stream, headerRowIndex, isCompatible);
}
}
#endregion
#region 公共转换方法
/// <summary>
/// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
/// </summary>
/// <param name="index">列索引</param>
/// <returns>列名，如第0列为A，第1列为B...</returns>
public static string ConvertColumnIndexToColumnName(int index)
{
index = index + 1;
int system = 26;
char[] digArray = new char[100];
int i = 0;
while (index > 0)
{
int mod = index % system;
if (mod == 0) mod = system;
digArray[i++] = (char)(mod - 1 + 'A');
index = (index - 1) / 26;
}
StringBuilder sb = new StringBuilder(i);
for (int j = i - 1; j >= 0; j--)
{
sb.Append(digArray[j]);
}
return sb.ToString();
}
/// <summary>
/// 转化日期
/// </summary>
/// <param name="date">日期</param>
/// <returns></returns>
public static DateTime ConvertDate(object date)
{
string dtStr = (date ?? "").ToString();
DateTime dt = new DateTime();
if (DateTime.TryParse(dtStr, out dt))
{
return dt;
}
try
{
string spStr = "";
if (dtStr.Contains("-"))
{
spStr = "-";
}
else if (dtStr.Contains("/"))
{
spStr = "/";
}
string[] time = dtStr.Split(spStr.ToCharArray());
int year = Convert.ToInt32(time[2]);
int month = Convert.ToInt32(time[0]);
int day = Convert.ToInt32(time[1]);
string years = Convert.ToString(year);
string months = Convert.ToString(month);
string days = Convert.ToString(day);
if (months.Length == 4)
{
dt = Convert.ToDateTime(date);
}
else
{
string rq = "";
if (years.Length == 1)
{
years = "0" + years;
}
if (months.Length == 1)
{
months = "0" + months;
}
if (days.Length == 1)
{
days = "0" + days;
}
rq = "20" + years + "-" + months + "-" + days;
dt = Convert.ToDateTime(rq);
}
}
catch
{
throw new Exception("日期格式不正确，转换日期失败！");
}
return dt;
}
/// <summary>
/// 转化数字
/// </summary>
/// <param name="d">数字字符串</param>
/// <returns></returns>
public static decimal ConvertDecimal(object d)
{
string dStr = (d ?? "").ToString();
decimal result = 0;
if (decimal.TryParse(dStr, out result))
{
return result;
}
else
{
throw new Exception("数字格式不正确，转换数字失败！");
}
}
#endregion
}
}
3、最近工作导数据失败，找问题原因：ORALCE导出数据，超出5w多条数据，由于数据量过大（加上列数多），无法导出。
PLSQL工具导出EXCEL失败，卡死白屏。
使用NPOI第三方类导出EXCEL失败，超出100+M多的数据就抛出异常提示内存溢出。
最后找到方法，使用OLEDB方式，数据导出到EXCEL文件，数据量大的时候，运行占用内存明显比NPOI和微软组件小（不超过100M）。
/// <summary>
/// 使用OLEDB导出Excel
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="filepath">文件目录和文件名</param>
/// <param name="tablename">SHEET页名称</param>
/// <param name="pagecount">每页记录数</param>
public static void Export(DataTable dt, string filepath, string tablename, int pagecount)
{
//excel 2003格式
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
//Excel 2007格式
//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
con.Open();
//开始分页
if (dt.Rows.Count > pagecount)
{
int page = dt.Rows.Count / pagecount + 1; //总页数
for (int i = 0; i < page; i++)
{
//建新sheet和表头
StringBuilder strSQL = new StringBuilder();
string tabname = tablename + i.ToString();
strSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000项建一页
strSQL.Append("(");
for (int j = 0; j < dt.Columns.Count; j++)
{
strSQL.Append("[" + dt.Columns[j].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
//准备逐条插入数据
for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
{
if (i == 0 || j < dt.Rows.Count)
{
StringBuilder tmp = new StringBuilder();
StringBuilder strfield = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int z = 0; z < dt.Columns.Count; z++)
{
strfield.Append("[" + dt.Columns[z].ColumnName + "]");
strvalue.Append("'" + dt.Rows[j][z].ToString() + "'");
if (z != dt.Columns.Count - 1)
{
strfield.Append(",");
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = tmp.Append(" insert into [" + tabname + "]( ")
.Append(strfield.ToString())
.Append(") values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
}
}
}
con.Close();
no = count;
}
Console.WriteLine("OK");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
GC.Collect();
}
4、NPOI导出Excel和EPPlus导出Excel比较
系统中经常会使用导出Excel的功能。
之前使用的是NPOI，但是导出数据行数多就报内存溢出。
最近看到EPPlus可以用来导出Excel，就自己测了下两者导出上的差异。
NPIO官网地址：http://npoi.codeplex.com/
EPPlus官网地址：http://epplus.codeplex.com/
添加NPOI、EPPlus类库dll使用的是NuGet添加。
在类库References右键Manage NuGet Packages…，之后选择添加对应的dll。
测试结果显示，相同数据结构的数据，EPPlus的导出能力比NPOI强。
20列，NPOI能导出4万数据，导出5万数据时报内存溢出。
EPPlus能导出20万以上数据，导出23万测试时内存溢出。
NPOI导出：
1 private static MemoryStream ExportXlsx(DataTable dt)
2 {
3 XSSFWorkbook workbook = new XSSFWorkbook();
4 ISheet sheet = null;
5
6 int headRowIndex = 0;
7 string sheetName = "Sheet1";
8 if (!string.IsNullOrEmpty(dt.TableName))
9 {
10 sheetName = dt.TableName;
11 }
12 sheet = workbook.CreateSheet(sheetName);
13 int rowIndex = 0;
14
15 #region 列头及样式
16 {
17 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);
18
19 ICellStyle headStyle = workbook.CreateCellStyle();
20 headStyle.Alignment = HorizontalAlignment.Center;
21 IFont font = workbook.CreateFont();
22 font.FontHeightInPoints = 10;
23 font.Boldweight = 700;
24 headStyle.SetFont(font);
25
26 foreach (DataColumn column in dt.Columns)
27 {
28 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
29 headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
30 }
31 }
32 #endregion
33
34 #region 填充内容
35
36 foreach (DataRow row in dt.Rows)
37 {
38 rowIndex++;
39 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
40 foreach (DataColumn column in dt.Columns)
41 {
42 string drValue = row[column].ToString();
43 dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
44 }
45 }
46 #endregion
47
48
49 MemoryStream ms = new MemoryStream();
50
51 workbook.Write(ms);
52 ms.Flush();
53
54 return ms;
55 }
56
57 public static void ExportXlsxByWeb(DataTable dt, string strFileName)
58 {
59
60 HttpContext curContext = HttpContext.Current;
61
62 MemoryStream ms = ExportXlsx(dt);
63
64 curContext.Response.AppendHeader("Content-Disposition",
65 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
66 curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
67 curContext.Response.ContentEncoding = Encoding.UTF8;
68
69 curContext.Response.BinaryWrite(ms.ToArray());
70 ms.Close();
71 ms.Dispose();
72 curContext.Response.End();
73
74 }
EPPlus导出：
1 /// <summary>
2 /// 使用EPPlus导出Excel(xlsx)
3 /// </summary>
4 /// <param name="sourceTable">数据源</param>
5 /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
6 public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
7 {
8 using (ExcelPackage pck = new ExcelPackage())
9 {
10 //Create the worksheet
11 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
12 ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
13
14 //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
15 ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
16
17 //Format the row
18 ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
19 Color borderColor = Color.FromArgb(155, 155, 155);
20
21 using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
22 {
23 rng.Style.Font.Name = "宋体";
24 rng.Style.Font.Size = 10;
25 rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
26 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
27
28 rng.Style.Border.Top.Style = borderStyle;
29 rng.Style.Border.Top.Color.SetColor(borderColor);
30
31 rng.Style.Border.Bottom.Style = borderStyle;
32 rng.Style.Border.Bottom.Color.SetColor(borderColor);
33
34 rng.Style.Border.Right.Style = borderStyle;
35 rng.Style.Border.Right.Color.SetColor(borderColor);
36 }
37
38 //Format the header row
39 using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
40 {
41 rng.Style.Font.Bold = true;
42 rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
43 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246)); //Set color to dark blue
44 rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
45 }
46
47 //Write it back to the client
48 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
49 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
50 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
51
52 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
53 HttpContext.Current.Response.End();
54 }
55 }
5导出数据到Excel（百万级）保存为csv格式
private void DataTableToCsv(DataTable table, string file)
{
StringBuilder titleBuilder = new StringBuilder();
StringBuilder lineBuilder = new StringBuilder();
FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
for (int i = 0; i < table.Columns.Count; i++)
{
titleBuilder.Append(table.Columns[i].ColumnName + "\t"); //栏位：自动跳到下一单元格
}
string title = titleBuilder.ToString();
title = title.Substring(0, title.Length - 1) + "\n";
sw.Write(title);
string line = string.Empty;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
lineBuilder.Append(row[i].ToString().Trim() + "\t"); //内容：自动跳到下一单元格
}
line = lineBuilder.ToString();
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
lineBuilder.Clear();
}
sw.Close();
fs.Close();
}
扫一扫
1632
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
