時(shí)間：2015/6/28來(lái)源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評(píng)論(0)
對(duì)于導(dǎo)出Execl文件大量時(shí) 一般做法時(shí)間會(huì)很長(zhǎng)， 下面?zhèn)�人收藏方法，可導(dǎo)海量數(shù)據(jù)
protected void CreateExecl(string swhere,string title) {
string saveFileName = Server.MapPath("https://www.cnblogs.com/uploads/file/" + title);
bool fileSaved = false;
Microsoft.office.Interop.excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null) {
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("tagno", typeof(string));
table.Columns.Add("AddDate", typeof(string));
SqlDataReader dr = mydb.ExecuteReaderSQL("SELECT tagno,AddDate FROM tagno WHERE " + swhere + " ORDER BY tagnoid ASC", null);
while (dr.Read()) {
table.Rows.Add(dr["tagno"].ToString(), dr["AddDate"].ToString());
}
dr.Close();
long rows = table.Rows.Count;
Int32 _pagecount = 60000;
if (rows > 60000) {
long pageRows = _pagecount;//定義每頁(yè)顯示的行數(shù),行數(shù)必須小于
int scount = (int)(rows / pageRows);
if (scount * pageRows < table.Rows.Count)//當(dāng)總行數(shù)不被pageRows整除時(shí)，經(jīng)過(guò)四舍五入可能頁(yè)數(shù)不準(zhǔn)
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++) {
if (sc > 1) {
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一個(gè)sheet
}
else {
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, 2];
datas[0, 0] = "吊牌號(hào)";
datas[0, 1] = "生成時(shí)間";
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
Int32 result;
if (pageRows * sc >= table.Rows.Count) {
result = table.Rows.Count;
}
else {
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++) {
index = index + 1;
for (int i = 0; i < 2; i++) {
object obj = table.Rows[r][i];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();
}
}
Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index +1, 2]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)。
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, 2]);
//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else {
string[,] datas = new string[table.Rows.Count + 2, 2];
datas[0, 0] = "吊牌號(hào)";
datas[0, 1] = "生成時(shí)間";
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < table.Rows.Count; r++) {
for (int i = 0; i < 2; i++) {
//if (gridview.GetVisibleColumn(i).ColumnType == typeof(string) || gridview.GetVisibleColumn(i).ColumnType == typeof(Decimal) || gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))
//{
object obj = table.Rows[r][i];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();
//}
}
}
Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, 2]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)。
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, 2]);
//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
if (saveFileName != "") {
//try
//{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
//}
//catch (Exception ex)
//{
// fileSaved = false;
//}
}
else {
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//強(qiáng)行銷(xiāo)毀
if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打開(kāi)EXCEL
}
關(guān)鍵詞標(biāo)簽：.net,execl
相關(guān)閱讀
熱門(mén)文章
手把手教你用好LINQ to SQL
在.NET環(huán)境下為網(wǎng)站增加IP過(guò)濾功能
ASP.NET 如何避免頁(yè)面重新整理時(shí)重復(fù)送出
用Asp.net擴(kuò)展ExtJS
人氣排行 asp.net表單提交方法GET\POST在ASP.NET中如何判斷用戶IE瀏覽器的版本Asp.net中messagebox的實(shí)現(xiàn)方法Asp.net中的web.config配置在ASP.NET MVC中實(shí)現(xiàn)大文件異步上傳用Iformattable接口控制.Net中文本格式c#.Net經(jīng)典面試題目用Asp.net擴(kuò)展ExtJS