時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(0)
對于導(dǎo)出Execl文件大量時 一般做法時間會很長， 下面?zhèn)€人收藏方法，可導(dǎo)海量數(shù)據(jù)
protected void CreateExecl(string swhere,string title) {
string saveFileName = Server.MapPath("http://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;//定義每頁顯示的行數(shù),行數(shù)必須小于
int scount = (int)(rows / pageRows);
if (scount * pageRows < table.Rows.Count)//當(dāng)總行數(shù)不被pageRows整除時，經(jīng)過四舍五入可能頁數(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);//添加一個sheet
}
else {
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, 2];
datas[0, 0] = "吊牌號";
datas[0, 1] = "生成時間";
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] = "吊牌號";
datas[0, 1] = "生成時間";
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)行銷毀
if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打開EXCEL
}
關(guān)鍵詞標(biāo)簽：.net,execl
相關(guān)閱讀
熱門文章
誅仙3飛升任務(wù)怎么做-誅仙3飛升任務(wù)流程最新2022
鐘離圣遺物推薦-原神鐘離圣遺物詞條
解決方法：應(yīng)用程序“DEFAULT WEB SITE”中的服務(wù)器錯誤
使用aspnet_regiis.exe 重新注冊.NET Framework
人氣排行 誅仙3飛升任務(wù)怎么做-誅仙3飛升任務(wù)流程最新2022 asp.net表單提交方法GET\POST 在ASP.NET中如何判斷用戶IE瀏覽器的版本 Asp.net中messagebox的實現(xiàn)方法 Asp.net中的web.config配置 在ASP.NET MVC中實現(xiàn)大文件異步上傳 asp.net獲取URL和IP地址 FileUpload上傳多文件出現(xiàn)錯誤的解決方法