NPOI大数据分批写入同个Excel
要导出来的数据库数据量很大,一次取出来压力有点大,故分批取出来,导入到同一个Excel。
因为Excel2003版最大行数是65536行,Excel2007开始的版本最大行数是1048576行,故NPOI导出时候选择了Excel2007。
Form1.cs
[csharp] view plaincopyPRint?
-
-
-
-
-
-
-
-
-
-
public Form1()
-
{
-
InitializeComponent();
-
List<DictionaryEntry> list = new List<DictionaryEntry>(){
-
new DictionaryEntry(1, "XA"),
-
new DictionaryEntry(2, "XB")
-
};
-
cbType.BindComboBox(list);
-
}
-
-
private void CreateExcel(string fileName)
-
{
-
if (File.Exists(fileName))
-
File.Delete(fileName);
-
-
IWorkbook workbook = new XSSFWorkbook();
-
ISheet sheet = workbook.CreateSheet("Sheet1");
-
FileStream sw = File.Create(fileName);
-
workbook.Write(sw);
-
sw.Close();
-
}
-
-
-
private void btnExport_Click(object sender, EventArgs e)
-
{
-
try
-
{
-
Task.Factory.StartNew(() =>
-
{
-
txtSql.SafeCall(() =>
-
{
-
txtSql.AppendText("开始处理...\r\n");
-
});
-
-
BusinessType businessType = GetBusinessType();
-
string[] sqlWhereArray = Sql.SqlWhereArray;
-
string[] DateRemarkArray = Sql.DateRemarkArray;
-
string fileName = string.Format("{0}.xlsx", businessType.ToString());
-
-
CreateExcel(fileName);
-
-
string sqlCount = Sql.GetRecordSql(businessType, "");
-
int recordCount = db.ExecuteScalar(sqlCount);
-
int sqlIndex = 0;
-
int rowIndex = 0;
-
foreach (string sqlWhre in sqlWhereArray)
-
{
-
sqlIndex++;
-
FileStream fs = File.Open(fileName, FileMode.Open);
-
IWorkbook workbook = new XSSFWorkbook(fs);
-
ISheet sheet = workbook.GetSheetAt(0);
-
txtSql.SafeCall(() =>
-
{
-
txtSql.AppendText("条件" + sqlIndex.ToString() + ":" + DateRemarkArray[sqlIndex - 1]);
-
});
-
string sql = Sql.GetDataSql(businessType, sqlWhre);
-
DataTable dt = db.GetDataSet(sql).Tables[0];
-
-
int columnsCount = dt.Columns.Count;
-
if (sqlIndex == 1)
-
{
-
IRow row0 = sheet.CreateRow(0);
-
for (int m = 0; m < columnsCount; m++)
-
{
-
DataColumn dc = dt.Columns[m];
-
row0.CreateCell(m).SetCellValue(dc.ColumnName);
-
}
-
}
-
-
for (int i = 0; i < dt.Rows.Count; i++)
-
{
-
rowIndex++;
-
DataRow dr = dt.Rows[i];
-
IRow row = sheet.CreateRow(rowIndex);
-
for (int j = 0; j < columnsCount; j++)
-
{
-
row.CreateCell(j).SetCellValue(dr[j].ToString());
-
}
-
-
lblMsg.SafeCall(() =>
-
{
-
if(i == (dt.Rows.Count - 1))
-
txtSql.AppendText(" 行数:" + (i+1).ToString() + "\r\n");
-
lblMsg.Text = string.Format("正在导出第{0}个条件,第{1}行", sqlIndex.ToString(), (i + 1).ToString());
-
double x = rowIndex * 1.0 / recordCount * 100;
-
lblProgress.Text = string.Format("总行数:{0}, 当前完成总{1}行,百分占比:{2} %", recordCount.ToString(), rowIndex.ToString(), x.ToString("#0.0"));
-
});
-
}
-
FileStream outFs = new FileStream(fileName, FileMode.Open);
-
workbook.Write(outFs);
-
outFs.Close();
-
}
-
}).ContinueWith(TaskEnded);
-
}
-
catch (Exception ex)
-
{
-
MessageBox.Show("发生异常,错误提示:" + ex.Message);
-
}
-
}
-
private void TaskEnded(Task task)
-
{
-
txtSql.SafeCall(() =>
-
{
-
lblMsg.Text = "全部导出完成!";
-
txtSql.AppendText("处理完成!\r\n");
-
});
-
}
Extensions.cs
-
public static class Extensions
-
{
-
public static void SafeCall(this Control ctrl, Action callback)
-
{
-
if (ctrl.InvokeRequired)
-
ctrl.Invoke(callback);
-
else
-
callback();
-
}
-
public static void BindComboBox(this ComboBox cb, List<DictionaryEntry> list)
-
{
-
cb.DisplayMember = "Value";
-
cb.ValueMember = "Key";
-
cb.DataSource = list;
-
}
-
}
Sql.cs
-
using System;
-
using System.Collections.Generic;
-
using System.Linq;
-
using System.Text;
-
-
namespace DataExport
-
{
-
public enum BusinessType
-
{
-
XA = 1,
-
XB = 2
-
}
-
-
public class Sql
-
{
-
-
-
-
public static string[] SqlWhereArray = {
-
" 条件1 ",
-
" 条件2 ",
-
" 条件3 "
-
};
-
-
-
-
-
public static string[] DateRemarkArray = {
-
"20130101至20130331",
-
"20130401至20130630",
-
"20130701后",
-
};
-
-
-
-
-
-
-
-
-
private static string GetSql(BusinessType type, string columns, string sqlWhere)
-
{
-
string sql = "";
-
switch (type)
-
{
-
case BusinessType.XA:
-
sql = string.Format(@"SELECT {0} FROMM tb1 WHERE 1=1 {1} ", columns, sqlWhere);
-
break;
-
case BusinessType.XB:
-
sql = string.Format(@"SELECT {0} FROMM tb2 WHERE 1=1 {1} ", columns, sqlWhere);
-
break;
-
}
-
-
-
return sql;
-
}
-
-
-
-
-
-
-
-
public static string GetRecordSql(BusinessType type, string sqlWhere)
-
{
-
string columns = "count(*)";
-
return GetSql(type, columns, sqlWhere);
-
}
-
-
-
-
-
-
-
-
public static string GetDataSql(BusinessType type, string sqlWhere)
-
{
-
string columns = "";
-
switch (type)
-
{
-
case BusinessType.XA:
-
columns = @"
-
col1 列1,
-
col2 列2,
-
col3 列3
-
";
-
break;
-
case BusinessType.XB:
-
columns = @"
-
col1 列1,
-
col2 列2
-
";
-
break;
-
-
}
-
return GetSql(type, columns, sqlWhere);
-
}
-
}
-
}
备注:
NPOI组件下载:http://download.csdn.net/detail/gdjlc/5820023
=========================================================
另外,NPOI只有 2.0以上版本才支持Excel 2007,用法
//自动辨别2007或2003
IWorkbook workbook = WorkbookFactory.Create(FileUpload1.FileContent);
//2007
XSSFWorkbook workbook = new XSSFWorkbook(FileUpload1.FileContent);
//2003
HSSFWorkbook workbook = new HSSFWorkbook(FileUpload1.FileContent);
CDA数据分析师考试相关入口一览(建议收藏):
▷ 想报名CDA认证考试,点击>>>
“CDA报名”
了解CDA考试详情;
▷ 想加入CDA考试题库,点击>>> “CDA题库” 了解CDA考试详情;
▷ 想学习CDA考试教材,点击>>> “CDA教材” 了解CDA考试详情;
▷ 想查询CDA考试成绩,点击>>> “CDA成绩” 了解CDA考试详情;
▷ 想了解CDA考试含金量,点击>>> “CDA含金量” 了解CDA考试详情;
▷ 想获取CDA考试时间/费用/条件/大纲/通过率,点击 >>>“CDA考试官网” 了解CDA考试详情;