新接到需求,要求将数据从开始到现在的都导出来,我随便抽取一个块,都是30W以上,而且,用户很“变态”,从数据库读出,然后导出到本地,必须控制在两分钟之内。我的乖乖啊,目前从数据库到DataTable,这块已经控制在1分钟以内,所以不考虑这个难度了,现在就是导出的事。
首先想到用NPOI,代码如下:
private void button2_Click(object sender, EventArgs e)
{//导出数据列 实现根据添加顺序导出列
NPOIHelper.ListColumnsName = new SortedList(new NoSort());
Console.WriteLine("开始读库:"+DateTime.Now.ToLongTimeString());
DataTable dt = getContractImportTable(GetSql());
Console.WriteLine("读库完毕:共"+dt.Rows.Count+"行。 " + DateTime.Now.ToLongTimeString());
for (int i = 0; i < dgvRSJ.Columns.Count; i++)
{
if (dgvRSJ.Columns[i].Visible == true)
{
NPOIHelper.ListColumnsName.Add(dgvRSJ.Columns[i].DataPropertyName,dgvRSJ.Columns[i].HeaderText);
}
}
string NodeLstID = GetParentParam();
NPOIHelper.ExportExcel(dt, "c:\\" + NodeLstID + ".xls");
Console.WriteLine("文件完毕:" + DateTime.Now.ToLongTimeString());
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
namespace GAS_SCYX.SJCX
{
public class NPOIHelper
{
/// <summary>
/// 导出列名
/// </summary>
public static System.Collections.SortedList ListColumnsName;
//格式化显示
static HSSFCellStyle cellDateStyle;
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="filePath"></param>
public static void ExportExcel(DataTable dtSource, string filePath)
{
if (ListColumnsName == null || ListColumnsName.Count == 0)
throw (new Exception("请对ListColumnsName设置要导出的列明!"));
HSSFWorkbook excelWorkbook = CreateExcelFile();
cellDateStyle = (HSSFCellStyle)excelWorkbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)excelWorkbook.CreateDataFormat();
cellDateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");//yyyy-mm-dd hh:mm:ss
InsertRow(dtSource, excelWorkbook);
SaveExcelFile(excelWorkbook, filePath);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="filePath"></param>
public static void ExportExcel(DataTable dtSource, Stream excelStream)
{
if (ListColumnsName == null || ListColumnsName.Count == 0)
throw (new Exception("请对ListColumnsName设置要导出的列明!"));
HSSFWorkbook excelWorkbook = CreateExcelFile();
InsertRow(dtSource, excelWorkbook);
SaveExcelFile(excelWorkbook, excelStream);
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="excelWorkBook"></param>
/// <param name="filePath"></param>
protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
{
FileStream file = null;
try
{
file = new FileStream(filePath, FileMode.Create);
excelWorkBook.Write(file);
}
finally
{
if (file != null)
{
file.Close();
}
}
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="excelWorkBook"></param>
/// <param name="filePath"></param>
protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
{
try
{
excelWorkBook.Write(excelStream);
}
finally
{
}
}
/// <summary>
/// 创建Excel文件
/// </summary>
/// <param name="filePath"></param>
protected static HSSFWorkbook CreateExcelFile()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
return hssfworkbook;
}
/// <summary>
/// 创建excel表头
/// </summary>
/// <param name="dgv"></param>
/// <param name="excelSheet"></param>
public static void CreateHeader(HSSFSheet excelSheet)
{
int cellIndex = 0;
HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);
//循环导出列
foreach (System.Collections.DictionaryEntry de in ListColumnsName)
{
HSSFCell newCell = (HSSFCell)newRow.CreateCell(cellIndex);
newCell.SetCellValue(de.Value.ToString());
cellIndex++;
}
}
/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
{
DataView dv = new DataView(dtSource);
DataTable dtmc = dv.ToTable(true, new string[] { "well_common_name" });
int sheetCount = 1;
HSSFSheet newsheet = null;
//循环数据源导出数据集
foreach (DataRow drjh in dtmc.Rows)
{
int rowCount = 0;
string jh = drjh[0].ToString();
Console.WriteLine("井:" + jh + " " + DateTime.Now.ToLongTimeString());
newsheet = (HSSFSheet)excelWorkbook.CreateSheet(jh);
CreateHeader(newsheet);
DataRow[] rows = dtSource.Select("well_common_name='" + jh + "'", "Prod_date");
if (rows.Length > 0)
{
foreach (DataRow dr in rows)
{//一个井号的集合
rowCount++;
HSSFRow newRow = (HSSFRow)newsheet.CreateRow(rowCount);
InsertCell(dr, newRow, newsheet, excelWorkbook);
}
sheetCount++;
}
}
Console.WriteLine("文件sheet个数:" + sheetCount.ToString() + " " + DateTime.Now.ToLongTimeString());
//foreach (DataRow dr in dtSource.Rows)
//{
// rowCount++;
// //超出10000条数据 创建新的工作簿
// if (rowCount == 10000)
// {
// rowCount = 1;
// sheetCount++;
// newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
// CreateHeader(newsheet);
// }
// HSSFRow newRow = newsheet.CreateRow(rowCount);
// InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook);
//}
}
/// <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(DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
HSSFCell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String"://字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
newCell.CellStyle = cellDateStyle;
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = (HSSFCell)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 = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + ":类型数据无法处理!"));
}
}
}
}
//排序实现接口 不进行排序 根据添加顺序导出
public class NoSort : System.Collections.IComparer
{
public int Compare(object x, object y)
{
return -1;
}
}
}
虽然10W以内没问题,时间也很精准,刚好两分钟以内,但是一旦超过30W,数据量过大,就会报“OutOfMemory”。很恼火啊!