ASP.NET操作EXCEL的總結(jié)篇
更新時(shí)間：2011年02月15日 00:58:20 作者：
今年有個(gè)系統(tǒng)的部分EXCEL的操作也讓我做，順便結(jié)合之前操作EXCEL的經(jīng)驗(yàn)作一下總結(jié)，可能也算不上什么，對(duì)于絕大多數(shù)來說也沒什么技術(shù)含量，網(wǎng)上一搜一大把，但我想還是有必要總結(jié)一下
公元19XX年前，關(guān)于EXCEL的操作就如滔滔江水，連綿不絕，真正操作EXCEL我也是從去年下半年開始的，有些比較復(fù)雜的年度報(bào)表之類的，做起來也有點(diǎn)費(fèi)力，不過還是都能畫出來了，關(guān)于EXCEL的報(bào)表導(dǎo)出，考慮到導(dǎo)出耗時(shí)的問題我主要采用AJAX來做的，分別捕捉幾個(gè)起止?fàn)顟B(tài)，給客戶端提示3個(gè)狀態(tài)：正在檢索數(shù)據(jù)。。。---》準(zhǔn)備導(dǎo)出數(shù)據(jù)。。。(只是從數(shù)據(jù)庫成功取出，還沒有讀寫excel文件)--》正在讀寫文件--》導(dǎo)出數(shù)據(jù)成功，當(dāng)然如果哪一過程出錯(cuò)，都有對(duì)應(yīng)的提示，只所以想到寫這篇文章，主要是因?yàn)榻衲暧袀€(gè)系統(tǒng)的部分EXCEL的操作也讓我做，順便結(jié)合之前操作EXCEL的經(jīng)驗(yàn)作一下總結(jié)，可能也算不上什么，對(duì)于絕大多數(shù)來說也沒什么技術(shù)含量，網(wǎng)上一搜一大把，但我想還是有必要總結(jié)一下，至少能給園子里的新手些許幫助，OK,Let's Go...
一. 程序操作EXCEL的應(yīng)用主要還是在統(tǒng)計(jì)報(bào)表方面，您可能會(huì)考慮讀EXCEL模板，也可能會(huì)考慮沒必要讀模板，其實(shí)讀不讀模板都能達(dá)到一樣的效果，看實(shí)際情況而用了。
1. 讀模板的話，首先模板存放在某個(gè)路徑下，根據(jù)模板把從數(shù)據(jù)庫里取出的數(shù)據(jù)寫回EXCEL然后生成一個(gè)新的EXCEL存放都另一個(gè)路徑以供下載，模板不變。
我這里的EXCEL操作主要是在VS2005里的，VS2003也可以的，不過沒怎么研究03里的操作(文章最后我會(huì)把05的示例下載地址貼上 那個(gè)demo里之前打包忘了放了一個(gè)生成數(shù)據(jù)的文件，剛放進(jìn)去了，不加也是可以運(yùn)行的，還有模板文件的數(shù)據(jù)稍微過濾了下重新放了部分對(duì)照看下）vs05中操作EXCEL直接引用.NET自帶的COM組件
，添加后項(xiàng)目的bin目錄下會(huì)自動(dòng)出現(xiàn)
Interop.Excel.dll這個(gè)DLL（需安裝office2003 excel，下面的說明及示例都是基于office2003的，版本不同調(diào)用可能會(huì)不一樣）
頁面的命名空間引用 using Excel;
下面是調(diào)用模板的一段代碼
#region 使用模板導(dǎo)出Excel表
case "ReportByTemp":
{
DataView dv = Cache["ReportByTemp"] as DataView;
//建立一個(gè)Excel.Application的新進(jìn)程
Excel.Application app = new Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(template_path + "\\EXCEL測(cè)試模板.xls");//這里的Add方法里的參數(shù)就是模板的路徑
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一個(gè)sheet表
if (worksheet == null)
{
return;
}
int rowNum = 0;
for (int i = 0; i < dv.Count; i++)
{
rowNum = i + 1;
worksheet.Cells[3 + i, 1] = rowNum;
worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑體
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
tick = DateTime.Now.Ticks.ToString();
save_path = temp_path + "\\" + tick + ".xls";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//關(guān)閉Excel進(jìn)程
}
break;
#endregion
效果如下：
2. 不讀模板的話，調(diào)用的時(shí)候其實(shí)會(huì)繼承一個(gè)空白模板，然后寫入數(shù)據(jù)，程序畫表頭，最終達(dá)到一樣的效果，程序如下：
#region 不使用模板生成Excel表
case "ReportByNone":
{
DataView dv = Cache["ReportByNone"] as DataView;
//建立一個(gè)Excel.Application的新進(jìn)程
Excel.Application app = new Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//這里的Add方法里的參數(shù)就相當(dāng)于繼承了一個(gè)空模板(暫這樣理解吧)
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return;
}
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]).Merge(Missing.Value); //橫向合并
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = "導(dǎo)出EXCEL測(cè)試一";
excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑體
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色
excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字體大小
excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色連續(xù)邊框
worksheet.Cells[2, 1] = "序號(hào)";
worksheet.Cells[2, 2] = "公司";
worksheet.Cells[2, 3] = "部門";
excelOperate.SetBold(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); //黑體
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
excelOperate.SetHAlignRight(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]);
excelOperate.SetBgColor(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3], System.Drawing.Color.Silver);//背景色
int rowNum = 0;
for (int i = 0; i < dv.Count; i++)
{
rowNum = i + 1;
worksheet.Cells[3 + i, 1] = rowNum;
worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑體
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//設(shè)置邊框顏色，不然打印預(yù)覽，會(huì)非常不雅觀
}
excelOperate.SetColumnWidth(worksheet, "A", 10);
excelOperate.SetColumnWidth(worksheet, "B", 20);
excelOperate.SetColumnWidth(worksheet, "C", 20);
worksheet.Name = "導(dǎo)出EXCEL測(cè)試一";
tick = DateTime.Now.Ticks.ToString();
save_path = temp_path + "\\"+ tick + ".xls";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//關(guān)閉Excel進(jìn)程
}
break;
#endregion
效果如下：
以上我給了兩個(gè)最簡(jiǎn)單的操作說明，下面詳細(xì)說一下對(duì)于一些稍微復(fù)雜的報(bào)表的生成處理
二. 對(duì)于復(fù)雜的EXCEL報(bào)表的生成處理，無非是縱向合并相同的數(shù)據(jù)行及嵌套縱向合并等一些操作，下面就幾個(gè)具有針對(duì)性的報(bào)表作下說明.
1.要生成相對(duì)復(fù)雜的EXCEL表，在從數(shù)據(jù)庫取數(shù)據(jù)時(shí)，要注意先按照合理的要求排好序，有時(shí)候可能order by后面要跟好幾個(gè)字段，而且這幾個(gè)字段誰先誰后也要注意，因?yàn)檫@些會(huì)直接影響報(bào)表呈現(xiàn)的效果，比如你的EXCEL表要按月份統(tǒng)計(jì)國內(nèi)外的項(xiàng)目，顯示出來的時(shí)候要多個(gè)項(xiàng)目相同的人連續(xù)，那么排序就可能要這樣order by 月份，項(xiàng)目類別，用戶ID，項(xiàng)目ID（這是寫好的視圖，基于視圖來檢索的），這個(gè)排序的字段順序就不能變了，變了的話就不太好生成想要的形式了，如下圖：
這個(gè)也是動(dòng)態(tài)畫的，用了個(gè)簡(jiǎn)單的模板，模板就一個(gè)表頭，沒多大意義，除非表頭很復(fù)雜而且在列表中不需要重畫，考慮模板就比較好，向上面那個(gè)一月份國際的和其它月份的都是需要重畫表頭的。至于合并，如果不是嵌套的合并，我們可以在向模板循環(huán)寫數(shù)據(jù)的時(shí)候直接控制，比如下面一個(gè)簡(jiǎn)單的寫法：
for (i = 0; i < table.Rows.Count; i++)
{
bidName = table.Rows[index]["BIDNAME"].ToString();
if (table.Rows[i]["BIDNAME"].ToString() == bidName)
{
projNum++;
worksheet.Cells[5 + i, 2] = table.Rows[i]["PROJNO"];
worksheet.Cells[5 + i, 3] = table.Rows[i]["PROJNAME"];
worksheet.Cells[5 + i, 4] = table.Rows[i]["STAT_DATE"];
worksheet.Cells[5 + i, 5] = table.Rows[i]["PROJTYPE"];
worksheet.Cells[5 + i, 6] = table.Rows[i]["CONTENT"];
worksheet.Cells[5 + i, 7] = table.Rows[i]["OPENDT"];
worksheet.Cells[5 + i, 8] = table.Rows[i]["OPENADDRESS"];
worksheet.Cells[5 + i, 9] = table.Rows[i]["REV_DATE"];
worksheet.Cells[5 + i, 10] = table.Rows[i]["BID_UNIT"];
worksheet.Cells[5 + i, 11] = table.Rows[i]["AGT_AMOUNT"];
worksheet.Cells[5 + i, 12] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDSER_AMOUNT"];
worksheet.Cells[5 + i, 13] = table.Rows[i]["SENDDATE"];
worksheet.Cells[5 + i, 14] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDPRICE"];
worksheet.Cells[5 + i, 15] = table.Rows[i]["BOOKAMOUNT"];
worksheet.Cells[5 + i, 16] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BAIL_AMOUNT"];
worksheet.Cells[5 + i, 17] = table.Rows[i]["USERNAME"];
worksheet.Cells[5 + i, 18] = table.Rows[i]["SECOND_USER"];
worksheet.Cells[5 + i, 19] = "";
worksheet.get_Range(worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 19]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
continue;
}
worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + i - 1, 1]).Merge(Missing.Value); //將第一列按投標(biāo)單位合并
worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + rowid, 1]).Value2 = bidName + "(" + projNum.ToString() + "個(gè)項(xiàng)目)";//合并后的單元格內(nèi)容
合并單元格的時(shí)候也要注意一個(gè)問題，就是合并的單元格必須是為空的，不然在執(zhí)行合并時(shí)，會(huì)提示“合并后的單元格的值將丟失”，具體不這樣提示的，大致是這個(gè)意思，一般我們合并都單元格相同的內(nèi)容，在合并前我們先保存那個(gè)值，再清空后合并，上面的代碼中把worksheet.Cell[5+rowid,1]這里系列的單元格的值空出來了，沒寫數(shù)據(jù)，而且最后合并了再寫值，避免了去循環(huán)清空。
2.嵌套的合并向上面那樣做可能控制比較麻煩，而且思路可能很混亂，我們可以考慮先循環(huán)填充所有的數(shù)據(jù)，在循環(huán)出來要合并的列，比如像下面的這張表
先循環(huán)填充數(shù)據(jù)，如下：
int index = 0, rownum = 0;
string ProjNo = "";
for (i = 0; i < table.Rows.Count; i++)
{
ProjNo = table.Rows[index]["PROJNO"].ToString();
if (table.Rows[i]["PROJNO"].ToString() == ProjNo)
{
wksheet.Cells[3 + i, 1] = rownum + 1;
wksheet.Cells[3 + i, 2] = "'" + table.Rows[i]["PROJNO"]; //加上單引號(hào)保證以0開頭的字符原樣輸出
wksheet.Cells[3 + i, 3] = "'" + table.Rows[i]["PROJNAME"];
wksheet.Cells[3 + i, 4] = "'" + table.Rows[i]["PA_NAME"];
wksheet.Cells[3 + i, 5] = "'" + table.Rows[i]["BIDER_NAME"];
wksheet.Cells[3 + i, 6] = table.Rows[i]["BAIL_AMOUNT"];
wksheet.Cells[3 + i, 7] = table.Rows[i]["NOT_BACK"];
wksheet.get_Range(wksheet.Cells[3 + i, 1], wksheet.Cells[3 + i, 7]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
continue;
}
index = i;
rownum++;
i--;
}
下面合并前三列相同內(nèi)容的單元：
//合并前三列操作
int m = 1, rowid = 3, k;
string projName = "";
for (k = 3; k <= i + 2; k++)
{
if (Convert.ToInt32(wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2) == m)
{
ProjNo = wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2.ToString();
projName = wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2.ToString();
wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2 = "";
wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2 = "";
wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2 = "";
continue;
}
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m;
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo;
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;
m++;
rowid = k;
k--;
}
//跳出循環(huán)后合并最后一個(gè)招標(biāo)項(xiàng)目
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m;
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo;
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;
下面合并標(biāo)段列
//合并標(biāo)段列
index = 0; rowid = 3; //重置變量
string pa_name = string.Empty; //標(biāo)段名稱
for (k = 3; k <= i + 2; k++)
{
pa_name = table.Rows[index]["PA_NAME"].ToString();
if (wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2.ToString() == pa_name)
{
wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2 = "";
continue;
}
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name;
index = k - 3;
rowid = k;
k--;
}
//退出循環(huán)時(shí)合并最后一個(gè)項(xiàng)目的標(biāo)段
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name;
tick = DateTime.Now.ToString("yyyyMMddhhmmss");
save_path = temp_path + "\\" + tick + "保證金收退情況表.xls";
Session["BailBackID"] = tick + "保證金收退情況表.xls";
Session["_BailBack"] = "true";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//關(guān)閉Excel進(jìn)程
//DownLoad(save_path);
//Page_Close();
當(dāng)然，上面的操作中會(huì)進(jìn)行好幾次循環(huán)，在性能方面不太可取，園子里的兄弟也許會(huì)有更好的方法，小弟不吝賜教了
下面我們看下幾個(gè)效果圖：
（注意：這里提示的導(dǎo)出數(shù)據(jù)是指從數(shù)據(jù)庫成功取出數(shù)據(jù)，還沒有操作EXCEL對(duì)象，剛開始已經(jīng)說過了，當(dāng)然這個(gè)提示文字換成其它的也可以）
整個(gè)過程采用AJAX提示的，一來不刷新，二來導(dǎo)出時(shí)間比較長的話，可以給客戶一個(gè)良好的體驗(yàn)效果，否可，用戶一點(diǎn)導(dǎo)出按鈕，半天沒反應(yīng)也沒提示，客戶就覺得怎么這么慢的，是不是你們程序有問題，指責(zé)一大堆，有了這么些交互提示信息，讓客戶多等幾分鐘也能承受。
3.生成的表格包含多個(gè)sheet的操作，比如下面一種情況
繪制這張表的要求是根據(jù)選擇某年的幾月到幾月，生成這個(gè)幾個(gè)月的一個(gè)綜合情況的sheet，然后分別生成這幾個(gè)月的單獨(dú)的sheet表，生成上面表的模板，包含兩個(gè)sheet ，一個(gè)綜合月份的sheet和一個(gè)單獨(dú)月份的sheet，因?yàn)閱为?dú)月份的sheet表現(xiàn)形式都是一樣的，我們可以根據(jù)選擇的月份個(gè)數(shù)Copy幾個(gè)sheet就可以了
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(template_path + "\\招標(biāo)單位年度招標(biāo)情況逐月統(tǒng)計(jì)表.xls");
Sheets sheets = workbook.Worksheets;
_Worksheet Yearsheet = (_Worksheet)sheets.get_Item(1);
_Worksheet worksheet = (_Worksheet)sheets.get_Item(2);
if (worksheet == null)
{
return;
}
for (int i = 1; i < monthCount; i++)
worksheet.Copy(Missing.Value, workbook.Worksheets[2]);//月統(tǒng)計(jì)工作薄
Yearsheet的操作就不說了，和前面幾個(gè)一樣操作，關(guān)鍵是月份的sheet的生成，其實(shí)就是循環(huán)操作get_Item(i)，代碼如下
//////////////////////////////////////每月詳細(xì)統(tǒng)計(jì)////////////////////////////////////
int item_id = 2;
rowNum = 0; book_Amount = 0; index = 0;
bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空變量
_Worksheet ws = null;
for (int i = 0; i < tableMM.Rows.Count; i++)
{
rowNum++;
Month = tableMM.Rows[index]["DATE_MONTH"].ToString();
if (tableMM.Rows[i]["DATE_MONTH"].ToString() == Month)
{
ws = (_Worksheet)sheets.get_Item(item_id);
ws.Cells[3 + rowNum - 1, 1] = rowNum;
ws.Cells[3 + rowNum - 1, 2] = tableMM.Rows[i]["PROJNO"];
ws.Cells[3 + rowNum - 1, 3] = tableMM.Rows[i]["PROJNAME"];
ws.Cells[3 + rowNum - 1, 4] = tableMM.Rows[i]["BID_TYPE"];
ws.Cells[3 + rowNum - 1, 5] = tableMM.Rows[i]["BID_MODE"];
ws.Cells[3 + rowNum - 1, 6] = tableMM.Rows[i]["OPENDT"];
ws.Cells[3 + rowNum - 1, 7] = tableMM.Rows[i]["OPENADDRESS"];
ws.Cells[3 + rowNum - 1, 8] = tableMM.Rows[i]["BID_UNIT"];
ws.Cells[3 + rowNum - 1, 9] = tableMM.Rows[i]["NOTICE_NO"].ToString().Replace("神華國貿(mào)", "");
ws.Cells[3 + rowNum - 1, 10] = tableMM.Rows[i]["BOOKAMOUNT"];
ws.Cells[3 + rowNum - 1, 11] = tableMM.Rows[i]["BIDPRICE"] + "(萬" + tableMM.Rows[i]["CURRENCY"] + ")";
ws.Cells[3 + rowNum - 1, 12] = tableMM.Rows[i]["BIDSER_AMOUNT"] + "(萬" + tableMM.Rows[i]["CURRENCY"]+")";
ws.Cells[3 + rowNum - 1, 13] = tableMM.Rows[i]["AGT_AMOUNT"];
ws.Cells[3 + rowNum - 1, 14] = "";
ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
continue;
}
ws.Cells[1, 1] = year + "年" + bidName + GetMonth(Month) + "月份招標(biāo)項(xiàng)目情況一覽表";
//每月合計(jì)
sql = " SELECT CURRENCY, NVL(SUM(BIDPRICE),0) AS BIDPRICE,NVL(SUM(BOOKAMOUNT),0) AS BOOKAMOUNT,NVL(SUM(BIDSER_AMOUNT),0) AS BIDSER_AMOUNT,NVL(SUM(AGT_AMOUNT),0) AS AGT_AMOUNT FROM IBS_V_BID_MONTHLY_STAT" + SqlFilter +
" AND DATE_YEAR ='" + year + "' AND COMPANY_ID=" + biderID + " AND DATE_MONTH ='" + Month + "'" +
" GROUP BY CURRENCY";
System.Data.DataTable dt1 = OracleHelper.RetDataTable(sql);
for (int m = 0; m < dt1.Rows.Count; m++)
{
bid_Amount += dt1.Rows[m]["BIDPRICE"] + "(萬"+dt1.Rows[m]["CURRENCY"] + ")\r\t";
book_Amount += float.Parse(dt1.Rows[m]["BOOKAMOUNT"].ToString());
bidser_Amount += dt1.Rows[m]["BIDSER_AMOUNT"] + "(萬" + dt1.Rows[m]["CURRENCY"] + ")\r\t";
agent_Amount += float.Parse(dt1.Rows[m]["AGT_AMOUNT"].ToString());
}
ws.Cells[3 + rowNum - 1, 3] = "合 計(jì)";
ws.Cells[3 + rowNum - 1, 10] = book_Amount;
ws.Cells[3 + rowNum - 1, 11] = bid_Amount;
ws.Cells[3 + rowNum - 1, 12] = bidser_Amount;
ws.Cells[3 + rowNum - 1, 13] = agent_Amount;
ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ws.Name = GetMM(Month);
item_id++;
index = i; //匯總下一個(gè)月份的招標(biāo)項(xiàng)目
i--;
rowNum = 0; book_Amount = 0;
bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空變量
}
//跳出循環(huán)時(shí)進(jìn)行最后一個(gè)月份的項(xiàng)目匯總
用的是oracle數(shù)據(jù)庫，所以上面那個(gè)sql語句。。。 呵呵
============================================================================================
上面大致說得就差不多了，因?yàn)槭遣粩嘌h(huán)的什么的，可能對(duì)于大的數(shù)據(jù)量讀寫來說，比較好性能，如果大家有什么更好的方法，可以指點(diǎn)下，為了彌補(bǔ)等待時(shí)間過長，所以才結(jié)合了AJAX來處理。
最后我把做的一個(gè)小demo的鏈接帖出來給大家，還有一些空模板和對(duì)應(yīng)生成的數(shù)據(jù)表給大家對(duì)照看下，尤其相對(duì)復(fù)雜一些的表畫應(yīng)該是能畫出來的，主要看大家采用什么樣的方法，能少循環(huán)一次就盡量少循環(huán)，呵呵~~~
EXCEL模板讀寫說明
http://www.justlike.com.cn/upfiles/template_xls.rar
http://www.justlike.com.cn/upfiles/ExcelFiles.rar
http://www.justlike.com.cn/upfiles/ExcelReportDemo.rar
（說明：最后彈出下載文件的一個(gè)頁面一直想讓其自動(dòng)關(guān)掉，但是不行，如果不關(guān)掉，再點(diǎn)導(dǎo)出，不會(huì)彈出下載框，實(shí)際的處理中我們可以在導(dǎo)出旁邊放個(gè)下載按鈕，就像上面的效果圖里那樣，當(dāng)然可以點(diǎn)導(dǎo)出的時(shí)候讓其在網(wǎng)頁中直接打開，點(diǎn)下載的時(shí)候再彈出下載框，但是直接打開的話，文件需要生成在虛擬目錄下，不太安全，呵呵~~，看實(shí)際情況處理了）
==========================================================================================
今天補(bǔ)充說明下，關(guān)于那個(gè)調(diào)用ajax回調(diào)的效果，有個(gè)地方用到了所謂的“ajax嵌套調(diào)用”，如下
function ExcelReportCallback(resp)
{
if(resp.value == "OK")
{
$('tipMsg').innerHTML = "<img border=\"0\" src=\"images/s_progressbar.gif\"><font color=#FF0000 style=font-weight:bold>準(zhǔn)備導(dǎo)出數(shù)據(jù),請(qǐng)稍等</font>";
setTimeout("RedirectUrl()",1000);//延時(shí)體驗(yàn)
}
else
if(resp.value == "NO")
{
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>沒有找到符合該查詢條件的數(shù)據(jù)</font>";
$('btnExcel').disabled = false;
}
else
{
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>警告:導(dǎo)出數(shù)據(jù)出錯(cuò)</font>";
$('btnExcel').disabled = false;
}
}
function RedirectUrl()
{
$('tipMsg').innerHTML = "<img border=\"0\" src=\"images/ajaxloading.gif\"><font color=#7fffd4 style=font-weight:bold>正在讀寫報(bào)表文件,請(qǐng)稍后</font>";
var ajax = new ajax_request("ExcelReport.aspx?flag=ReportByTemp&"+Math.random(), "", "", ReportCallback);
function ReportCallback(resp)
{
if(resp.value != "Error" && resp.value !="")
{
$('btnExcel').disabled = false;
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>數(shù)據(jù)導(dǎo)出成功!</font>";
Open("XLS_DownLoad.aspx?path="+resp.value);//window.location.href = resp.value;//
}
else
{
$('btnExcel').disabled = false;
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>文件讀寫出錯(cuò),請(qǐng)檢查文件模板是否存在或?qū)ξ募欠裼凶x寫權(quán)限!</font>";
}
}
}
function Open(url)
{
window.open(url,'newwindow','height=1,width=1,top=1500,left=1500,toolbar=no,menubar=no,scrollbars=yes,location=no,status=no')
}
ExcelReportCallback(resp)原本是一個(gè)回調(diào)函數(shù)，但是里面調(diào)用了一個(gè)RedirectUrl()方法，這個(gè)方法又包含了一個(gè)回調(diào)函數(shù)，這樣就形成了回調(diào)的嵌套，之所以這么做，是因?yàn)?，第一個(gè)回調(diào)是處理從數(shù)據(jù)庫取出數(shù)據(jù)成功與否，如果成功了跳轉(zhuǎn)到畫EXCEL的頁面，這樣的話會(huì)出現(xiàn)一個(gè)空白頁等生成好后出現(xiàn)下載框，后來覺得是否可以嵌套一個(gè)回調(diào)來繼續(xù)一次異步操作，這樣就不會(huì)出現(xiàn)長時(shí)間等待的空白頁面了，而是生成好EXCEL后返回地址，或者可以返回一個(gè)文件名到XLS_DownLoad.aspx頁面直接下載，但是XLS_DownLoad.aspx也是要出現(xiàn)的，我嘗試過讓下載后這個(gè)頁面自動(dòng)關(guān)閉，無賴做不到，所以把Open()方法里的數(shù)據(jù)值調(diào)得讓頁面不顯示，但是狀態(tài)欄還是有顯示的。
到這里算是寫完了，決定奢侈下，放到首頁下：），總覺得首頁的文章只有高手才能放，而且放到首頁也是一種奢侈，希望對(duì)園子里的某些人有一定的幫助吧~~
http://xiazai.jb51.net/201102/yuanma/ExcelReport.rar
一. 程序操作EXCEL的應(yīng)用主要還是在統(tǒng)計(jì)報(bào)表方面，您可能會(huì)考慮讀EXCEL模板，也可能會(huì)考慮沒必要讀模板，其實(shí)讀不讀模板都能達(dá)到一樣的效果，看實(shí)際情況而用了。
1. 讀模板的話，首先模板存放在某個(gè)路徑下，根據(jù)模板把從數(shù)據(jù)庫里取出的數(shù)據(jù)寫回EXCEL然后生成一個(gè)新的EXCEL存放都另一個(gè)路徑以供下載，模板不變。
我這里的EXCEL操作主要是在VS2005里的，VS2003也可以的，不過沒怎么研究03里的操作(文章最后我會(huì)把05的示例下載地址貼上 那個(gè)demo里之前打包忘了放了一個(gè)生成數(shù)據(jù)的文件，剛放進(jìn)去了，不加也是可以運(yùn)行的，還有模板文件的數(shù)據(jù)稍微過濾了下重新放了部分對(duì)照看下）vs05中操作EXCEL直接引用.NET自帶的COM組件
，添加后項(xiàng)目的bin目錄下會(huì)自動(dòng)出現(xiàn)Interop.Excel.dll這個(gè)DLL（需安裝office2003 excel，下面的說明及示例都是基于office2003的，版本不同調(diào)用可能會(huì)不一樣）頁面的命名空間引用 using Excel;
下面是調(diào)用模板的一段代碼
復(fù)制代碼 代碼如下:
#region 使用模板導(dǎo)出Excel表
case "ReportByTemp":
{
DataView dv = Cache["ReportByTemp"] as DataView;
//建立一個(gè)Excel.Application的新進(jìn)程
Excel.Application app = new Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(template_path + "\\EXCEL測(cè)試模板.xls");//這里的Add方法里的參數(shù)就是模板的路徑
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一個(gè)sheet表
if (worksheet == null)
{
return;
}
int rowNum = 0;
for (int i = 0; i < dv.Count; i++)
{
rowNum = i + 1;
worksheet.Cells[3 + i, 1] = rowNum;
worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑體
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
tick = DateTime.Now.Ticks.ToString();
save_path = temp_path + "\\" + tick + ".xls";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//關(guān)閉Excel進(jìn)程
}
break;
#endregion
效果如下：
2. 不讀模板的話，調(diào)用的時(shí)候其實(shí)會(huì)繼承一個(gè)空白模板，然后寫入數(shù)據(jù)，程序畫表頭，最終達(dá)到一樣的效果，程序如下：
復(fù)制代碼 代碼如下:
#region 不使用模板生成Excel表
case "ReportByNone":
{
DataView dv = Cache["ReportByNone"] as DataView;
//建立一個(gè)Excel.Application的新進(jìn)程
Excel.Application app = new Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//這里的Add方法里的參數(shù)就相當(dāng)于繼承了一個(gè)空模板(暫這樣理解吧)
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return;
}
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]).Merge(Missing.Value); //橫向合并
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = "導(dǎo)出EXCEL測(cè)試一";
excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑體
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色
excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字體大小
excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色連續(xù)邊框
worksheet.Cells[2, 1] = "序號(hào)";
worksheet.Cells[2, 2] = "公司";
worksheet.Cells[2, 3] = "部門";
excelOperate.SetBold(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]); //黑體
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
excelOperate.SetHAlignRight(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3]);
excelOperate.SetBgColor(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 3], System.Drawing.Color.Silver);//背景色
int rowNum = 0;
for (int i = 0; i < dv.Count; i++)
{
rowNum = i + 1;
worksheet.Cells[3 + i, 1] = rowNum;
worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑體
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//設(shè)置邊框顏色，不然打印預(yù)覽，會(huì)非常不雅觀
}
excelOperate.SetColumnWidth(worksheet, "A", 10);
excelOperate.SetColumnWidth(worksheet, "B", 20);
excelOperate.SetColumnWidth(worksheet, "C", 20);
worksheet.Name = "導(dǎo)出EXCEL測(cè)試一";
tick = DateTime.Now.Ticks.ToString();
save_path = temp_path + "\\"+ tick + ".xls";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//關(guān)閉Excel進(jìn)程
}
break;
#endregion
效果如下：
以上我給了兩個(gè)最簡(jiǎn)單的操作說明，下面詳細(xì)說一下對(duì)于一些稍微復(fù)雜的報(bào)表的生成處理
二. 對(duì)于復(fù)雜的EXCEL報(bào)表的生成處理，無非是縱向合并相同的數(shù)據(jù)行及嵌套縱向合并等一些操作，下面就幾個(gè)具有針對(duì)性的報(bào)表作下說明.
1.要生成相對(duì)復(fù)雜的EXCEL表，在從數(shù)據(jù)庫取數(shù)據(jù)時(shí)，要注意先按照合理的要求排好序，有時(shí)候可能order by后面要跟好幾個(gè)字段，而且這幾個(gè)字段誰先誰后也要注意，因?yàn)檫@些會(huì)直接影響報(bào)表呈現(xiàn)的效果，比如你的EXCEL表要按月份統(tǒng)計(jì)國內(nèi)外的項(xiàng)目，顯示出來的時(shí)候要多個(gè)項(xiàng)目相同的人連續(xù)，那么排序就可能要這樣order by 月份，項(xiàng)目類別，用戶ID，項(xiàng)目ID（這是寫好的視圖，基于視圖來檢索的），這個(gè)排序的字段順序就不能變了，變了的話就不太好生成想要的形式了，如下圖：
這個(gè)也是動(dòng)態(tài)畫的，用了個(gè)簡(jiǎn)單的模板，模板就一個(gè)表頭，沒多大意義，除非表頭很復(fù)雜而且在列表中不需要重畫，考慮模板就比較好，向上面那個(gè)一月份國際的和其它月份的都是需要重畫表頭的。至于合并，如果不是嵌套的合并，我們可以在向模板循環(huán)寫數(shù)據(jù)的時(shí)候直接控制，比如下面一個(gè)簡(jiǎn)單的寫法：
復(fù)制代碼 代碼如下:
for (i = 0; i < table.Rows.Count; i++)
{
bidName = table.Rows[index]["BIDNAME"].ToString();
if (table.Rows[i]["BIDNAME"].ToString() == bidName)
{
projNum++;
worksheet.Cells[5 + i, 2] = table.Rows[i]["PROJNO"];
worksheet.Cells[5 + i, 3] = table.Rows[i]["PROJNAME"];
worksheet.Cells[5 + i, 4] = table.Rows[i]["STAT_DATE"];
worksheet.Cells[5 + i, 5] = table.Rows[i]["PROJTYPE"];
worksheet.Cells[5 + i, 6] = table.Rows[i]["CONTENT"];
worksheet.Cells[5 + i, 7] = table.Rows[i]["OPENDT"];
worksheet.Cells[5 + i, 8] = table.Rows[i]["OPENADDRESS"];
worksheet.Cells[5 + i, 9] = table.Rows[i]["REV_DATE"];
worksheet.Cells[5 + i, 10] = table.Rows[i]["BID_UNIT"];
worksheet.Cells[5 + i, 11] = table.Rows[i]["AGT_AMOUNT"];
worksheet.Cells[5 + i, 12] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDSER_AMOUNT"];
worksheet.Cells[5 + i, 13] = table.Rows[i]["SENDDATE"];
worksheet.Cells[5 + i, 14] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDPRICE"];
worksheet.Cells[5 + i, 15] = table.Rows[i]["BOOKAMOUNT"];
worksheet.Cells[5 + i, 16] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BAIL_AMOUNT"];
worksheet.Cells[5 + i, 17] = table.Rows[i]["USERNAME"];
worksheet.Cells[5 + i, 18] = table.Rows[i]["SECOND_USER"];
worksheet.Cells[5 + i, 19] = "";
worksheet.get_Range(worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 19]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
continue;
}
worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + i - 1, 1]).Merge(Missing.Value); //將第一列按投標(biāo)單位合并
worksheet.get_Range(worksheet.Cells[5 + rowid, 1], worksheet.Cells[5 + rowid, 1]).Value2 = bidName + "(" + projNum.ToString() + "個(gè)項(xiàng)目)";//合并后的單元格內(nèi)容
合并單元格的時(shí)候也要注意一個(gè)問題，就是合并的單元格必須是為空的，不然在執(zhí)行合并時(shí)，會(huì)提示“合并后的單元格的值將丟失”，具體不這樣提示的，大致是這個(gè)意思，一般我們合并都單元格相同的內(nèi)容，在合并前我們先保存那個(gè)值，再清空后合并，上面的代碼中把worksheet.Cell[5+rowid,1]這里系列的單元格的值空出來了，沒寫數(shù)據(jù)，而且最后合并了再寫值，避免了去循環(huán)清空。
2.嵌套的合并向上面那樣做可能控制比較麻煩，而且思路可能很混亂，我們可以考慮先循環(huán)填充所有的數(shù)據(jù)，在循環(huán)出來要合并的列，比如像下面的這張表
先循環(huán)填充數(shù)據(jù)，如下：
復(fù)制代碼 代碼如下:
int index = 0, rownum = 0;
string ProjNo = "";
for (i = 0; i < table.Rows.Count; i++)
{
ProjNo = table.Rows[index]["PROJNO"].ToString();
if (table.Rows[i]["PROJNO"].ToString() == ProjNo)
{
wksheet.Cells[3 + i, 1] = rownum + 1;
wksheet.Cells[3 + i, 2] = "'" + table.Rows[i]["PROJNO"]; //加上單引號(hào)保證以0開頭的字符原樣輸出
wksheet.Cells[3 + i, 3] = "'" + table.Rows[i]["PROJNAME"];
wksheet.Cells[3 + i, 4] = "'" + table.Rows[i]["PA_NAME"];
wksheet.Cells[3 + i, 5] = "'" + table.Rows[i]["BIDER_NAME"];
wksheet.Cells[3 + i, 6] = table.Rows[i]["BAIL_AMOUNT"];
wksheet.Cells[3 + i, 7] = table.Rows[i]["NOT_BACK"];
wksheet.get_Range(wksheet.Cells[3 + i, 1], wksheet.Cells[3 + i, 7]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
continue;
}
index = i;
rownum++;
i--;
}
下面合并前三列相同內(nèi)容的單元：
復(fù)制代碼 代碼如下:
//合并前三列操作
int m = 1, rowid = 3, k;
string projName = "";
for (k = 3; k <= i + 2; k++)
{
if (Convert.ToInt32(wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2) == m)
{
ProjNo = wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2.ToString();
projName = wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2.ToString();
wksheet.get_Range(wksheet.Cells[k, 1], wksheet.Cells[k, 1]).Value2 = "";
wksheet.get_Range(wksheet.Cells[k, 2], wksheet.Cells[k, 2]).Value2 = "";
wksheet.get_Range(wksheet.Cells[k, 3], wksheet.Cells[k, 3]).Value2 = "";
continue;
}
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m;
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo;
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;
m++;
rowid = k;
k--;
}
//跳出循環(huán)后合并最后一個(gè)招標(biāo)項(xiàng)目
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[k - 1, 1]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 1], wksheet.Cells[rowid, 1]).Value2 = m;
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[k - 1, 2]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 2], wksheet.Cells[rowid, 2]).Value2 = "'" + ProjNo;
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[k - 1, 3]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 3], wksheet.Cells[rowid, 3]).Value2 = "'" + projName;
下面合并標(biāo)段列
復(fù)制代碼 代碼如下:
//合并標(biāo)段列
index = 0; rowid = 3; //重置變量
string pa_name = string.Empty; //標(biāo)段名稱
for (k = 3; k <= i + 2; k++)
{
pa_name = table.Rows[index]["PA_NAME"].ToString();
if (wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2.ToString() == pa_name)
{
wksheet.get_Range(wksheet.Cells[k, 4], wksheet.Cells[k, 4]).Value2 = "";
continue;
}
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name;
index = k - 3;
rowid = k;
k--;
}
//退出循環(huán)時(shí)合并最后一個(gè)項(xiàng)目的標(biāo)段
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[k - 1, 4]).Merge(Missing.Value);
wksheet.get_Range(wksheet.Cells[rowid, 4], wksheet.Cells[rowid, 4]).Value2 = "'" + pa_name;
tick = DateTime.Now.ToString("yyyyMMddhhmmss");
save_path = temp_path + "\\" + tick + "保證金收退情況表.xls";
Session["BailBackID"] = tick + "保證金收退情況表.xls";
Session["_BailBack"] = "true";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//關(guān)閉Excel進(jìn)程
//DownLoad(save_path);
//Page_Close();
當(dāng)然，上面的操作中會(huì)進(jìn)行好幾次循環(huán)，在性能方面不太可取，園子里的兄弟也許會(huì)有更好的方法，小弟不吝賜教了
下面我們看下幾個(gè)效果圖：
（注意：這里提示的導(dǎo)出數(shù)據(jù)是指從數(shù)據(jù)庫成功取出數(shù)據(jù)，還沒有操作EXCEL對(duì)象，剛開始已經(jīng)說過了，當(dāng)然這個(gè)提示文字換成其它的也可以）
整個(gè)過程采用AJAX提示的，一來不刷新，二來導(dǎo)出時(shí)間比較長的話，可以給客戶一個(gè)良好的體驗(yàn)效果，否可，用戶一點(diǎn)導(dǎo)出按鈕，半天沒反應(yīng)也沒提示，客戶就覺得怎么這么慢的，是不是你們程序有問題，指責(zé)一大堆，有了這么些交互提示信息，讓客戶多等幾分鐘也能承受。
3.生成的表格包含多個(gè)sheet的操作，比如下面一種情況
繪制這張表的要求是根據(jù)選擇某年的幾月到幾月，生成這個(gè)幾個(gè)月的一個(gè)綜合情況的sheet，然后分別生成這幾個(gè)月的單獨(dú)的sheet表，生成上面表的模板，包含兩個(gè)sheet ，一個(gè)綜合月份的sheet和一個(gè)單獨(dú)月份的sheet，因?yàn)閱为?dú)月份的sheet表現(xiàn)形式都是一樣的，我們可以根據(jù)選擇的月份個(gè)數(shù)Copy幾個(gè)sheet就可以了
復(fù)制代碼 代碼如下:
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(template_path + "\\招標(biāo)單位年度招標(biāo)情況逐月統(tǒng)計(jì)表.xls");
Sheets sheets = workbook.Worksheets;
_Worksheet Yearsheet = (_Worksheet)sheets.get_Item(1);
_Worksheet worksheet = (_Worksheet)sheets.get_Item(2);
if (worksheet == null)
{
return;
}
for (int i = 1; i < monthCount; i++)
worksheet.Copy(Missing.Value, workbook.Worksheets[2]);//月統(tǒng)計(jì)工作薄
Yearsheet的操作就不說了，和前面幾個(gè)一樣操作，關(guān)鍵是月份的sheet的生成，其實(shí)就是循環(huán)操作get_Item(i)，代碼如下
復(fù)制代碼 代碼如下:
//////////////////////////////////////每月詳細(xì)統(tǒng)計(jì)////////////////////////////////////
int item_id = 2;
rowNum = 0; book_Amount = 0; index = 0;
bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空變量
_Worksheet ws = null;
for (int i = 0; i < tableMM.Rows.Count; i++)
{
rowNum++;
Month = tableMM.Rows[index]["DATE_MONTH"].ToString();
if (tableMM.Rows[i]["DATE_MONTH"].ToString() == Month)
{
ws = (_Worksheet)sheets.get_Item(item_id);
ws.Cells[3 + rowNum - 1, 1] = rowNum;
ws.Cells[3 + rowNum - 1, 2] = tableMM.Rows[i]["PROJNO"];
ws.Cells[3 + rowNum - 1, 3] = tableMM.Rows[i]["PROJNAME"];
ws.Cells[3 + rowNum - 1, 4] = tableMM.Rows[i]["BID_TYPE"];
ws.Cells[3 + rowNum - 1, 5] = tableMM.Rows[i]["BID_MODE"];
ws.Cells[3 + rowNum - 1, 6] = tableMM.Rows[i]["OPENDT"];
ws.Cells[3 + rowNum - 1, 7] = tableMM.Rows[i]["OPENADDRESS"];
ws.Cells[3 + rowNum - 1, 8] = tableMM.Rows[i]["BID_UNIT"];
ws.Cells[3 + rowNum - 1, 9] = tableMM.Rows[i]["NOTICE_NO"].ToString().Replace("神華國貿(mào)", "");
ws.Cells[3 + rowNum - 1, 10] = tableMM.Rows[i]["BOOKAMOUNT"];
ws.Cells[3 + rowNum - 1, 11] = tableMM.Rows[i]["BIDPRICE"] + "(萬" + tableMM.Rows[i]["CURRENCY"] + ")";
ws.Cells[3 + rowNum - 1, 12] = tableMM.Rows[i]["BIDSER_AMOUNT"] + "(萬" + tableMM.Rows[i]["CURRENCY"]+")";
ws.Cells[3 + rowNum - 1, 13] = tableMM.Rows[i]["AGT_AMOUNT"];
ws.Cells[3 + rowNum - 1, 14] = "";
ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
continue;
}
ws.Cells[1, 1] = year + "年" + bidName + GetMonth(Month) + "月份招標(biāo)項(xiàng)目情況一覽表";
//每月合計(jì)
sql = " SELECT CURRENCY, NVL(SUM(BIDPRICE),0) AS BIDPRICE,NVL(SUM(BOOKAMOUNT),0) AS BOOKAMOUNT,NVL(SUM(BIDSER_AMOUNT),0) AS BIDSER_AMOUNT,NVL(SUM(AGT_AMOUNT),0) AS AGT_AMOUNT FROM IBS_V_BID_MONTHLY_STAT" + SqlFilter +
" AND DATE_YEAR ='" + year + "' AND COMPANY_ID=" + biderID + " AND DATE_MONTH ='" + Month + "'" +
" GROUP BY CURRENCY";
System.Data.DataTable dt1 = OracleHelper.RetDataTable(sql);
for (int m = 0; m < dt1.Rows.Count; m++)
{
bid_Amount += dt1.Rows[m]["BIDPRICE"] + "(萬"+dt1.Rows[m]["CURRENCY"] + ")\r\t";
book_Amount += float.Parse(dt1.Rows[m]["BOOKAMOUNT"].ToString());
bidser_Amount += dt1.Rows[m]["BIDSER_AMOUNT"] + "(萬" + dt1.Rows[m]["CURRENCY"] + ")\r\t";
agent_Amount += float.Parse(dt1.Rows[m]["AGT_AMOUNT"].ToString());
}
ws.Cells[3 + rowNum - 1, 3] = "合 計(jì)";
ws.Cells[3 + rowNum - 1, 10] = book_Amount;
ws.Cells[3 + rowNum - 1, 11] = bid_Amount;
ws.Cells[3 + rowNum - 1, 12] = bidser_Amount;
ws.Cells[3 + rowNum - 1, 13] = agent_Amount;
ws.get_Range(ws.Cells[3 + rowNum - 1, 1], ws.Cells[3 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ws.Name = GetMM(Month);
item_id++;
index = i; //匯總下一個(gè)月份的招標(biāo)項(xiàng)目
i--;
rowNum = 0; book_Amount = 0;
bid_Amount = ""; bidser_Amount = ""; agent_Amount = 0;//清空變量
}
//跳出循環(huán)時(shí)進(jìn)行最后一個(gè)月份的項(xiàng)目匯總
用的是oracle數(shù)據(jù)庫，所以上面那個(gè)sql語句。。。 呵呵
============================================================================================
上面大致說得就差不多了，因?yàn)槭遣粩嘌h(huán)的什么的，可能對(duì)于大的數(shù)據(jù)量讀寫來說，比較好性能，如果大家有什么更好的方法，可以指點(diǎn)下，為了彌補(bǔ)等待時(shí)間過長，所以才結(jié)合了AJAX來處理。
最后我把做的一個(gè)小demo的鏈接帖出來給大家，還有一些空模板和對(duì)應(yīng)生成的數(shù)據(jù)表給大家對(duì)照看下，尤其相對(duì)復(fù)雜一些的表畫應(yīng)該是能畫出來的，主要看大家采用什么樣的方法，能少循環(huán)一次就盡量少循環(huán)，呵呵~~~
EXCEL模板讀寫說明
http://www.justlike.com.cn/upfiles/template_xls.rar
http://www.justlike.com.cn/upfiles/ExcelFiles.rar
http://www.justlike.com.cn/upfiles/ExcelReportDemo.rar
（說明：最后彈出下載文件的一個(gè)頁面一直想讓其自動(dòng)關(guān)掉，但是不行，如果不關(guān)掉，再點(diǎn)導(dǎo)出，不會(huì)彈出下載框，實(shí)際的處理中我們可以在導(dǎo)出旁邊放個(gè)下載按鈕，就像上面的效果圖里那樣，當(dāng)然可以點(diǎn)導(dǎo)出的時(shí)候讓其在網(wǎng)頁中直接打開，點(diǎn)下載的時(shí)候再彈出下載框，但是直接打開的話，文件需要生成在虛擬目錄下，不太安全，呵呵~~，看實(shí)際情況處理了）
==========================================================================================
今天補(bǔ)充說明下，關(guān)于那個(gè)調(diào)用ajax回調(diào)的效果，有個(gè)地方用到了所謂的“ajax嵌套調(diào)用”，如下
復(fù)制代碼 代碼如下:
function ExcelReportCallback(resp)
{
if(resp.value == "OK")
{
$('tipMsg').innerHTML = "<img border=\"0\" src=\"images/s_progressbar.gif\"><font color=#FF0000 style=font-weight:bold>準(zhǔn)備導(dǎo)出數(shù)據(jù),請(qǐng)稍等</font>";
setTimeout("RedirectUrl()",1000);//延時(shí)體驗(yàn)
}
else
if(resp.value == "NO")
{
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>沒有找到符合該查詢條件的數(shù)據(jù)</font>";
$('btnExcel').disabled = false;
}
else
{
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>警告:導(dǎo)出數(shù)據(jù)出錯(cuò)</font>";
$('btnExcel').disabled = false;
}
}
復(fù)制代碼 代碼如下:
function RedirectUrl()
{
$('tipMsg').innerHTML = "<img border=\"0\" src=\"images/ajaxloading.gif\"><font color=#7fffd4 style=font-weight:bold>正在讀寫報(bào)表文件,請(qǐng)稍后</font>";
var ajax = new ajax_request("ExcelReport.aspx?flag=ReportByTemp&"+Math.random(), "", "", ReportCallback);
function ReportCallback(resp)
{
if(resp.value != "Error" && resp.value !="")
{
$('btnExcel').disabled = false;
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>數(shù)據(jù)導(dǎo)出成功!</font>";
Open("XLS_DownLoad.aspx?path="+resp.value);//window.location.href = resp.value;//
}
else
{
$('btnExcel').disabled = false;
$('tipMsg').innerHTML = "<font color=#FF0000 style=font-weight:bold>文件讀寫出錯(cuò),請(qǐng)檢查文件模板是否存在或?qū)ξ募欠裼凶x寫權(quán)限!</font>";
}
}
}
function Open(url)
{
window.open(url,'newwindow','height=1,width=1,top=1500,left=1500,toolbar=no,menubar=no,scrollbars=yes,location=no,status=no')
}
ExcelReportCallback(resp)原本是一個(gè)回調(diào)函數(shù)，但是里面調(diào)用了一個(gè)RedirectUrl()方法，這個(gè)方法又包含了一個(gè)回調(diào)函數(shù)，這樣就形成了回調(diào)的嵌套，之所以這么做，是因?yàn)?，第一個(gè)回調(diào)是處理從數(shù)據(jù)庫取出數(shù)據(jù)成功與否，如果成功了跳轉(zhuǎn)到畫EXCEL的頁面，這樣的話會(huì)出現(xiàn)一個(gè)空白頁等生成好后出現(xiàn)下載框，后來覺得是否可以嵌套一個(gè)回調(diào)來繼續(xù)一次異步操作，這樣就不會(huì)出現(xiàn)長時(shí)間等待的空白頁面了，而是生成好EXCEL后返回地址，或者可以返回一個(gè)文件名到XLS_DownLoad.aspx頁面直接下載，但是XLS_DownLoad.aspx也是要出現(xiàn)的，我嘗試過讓下載后這個(gè)頁面自動(dòng)關(guān)閉，無賴做不到，所以把Open()方法里的數(shù)據(jù)值調(diào)得讓頁面不顯示，但是狀態(tài)欄還是有顯示的。
到這里算是寫完了，決定奢侈下，放到首頁下：），總覺得首頁的文章只有高手才能放，而且放到首頁也是一種奢侈，希望對(duì)園子里的某些人有一定的幫助吧~~
http://xiazai.jb51.net/201102/yuanma/ExcelReport.rar
您可能感興趣的文章:
- ASP.NET Core 導(dǎo)入導(dǎo)出Excel xlsx 文件實(shí)例
- asp.net生成Excel并導(dǎo)出下載五種實(shí)現(xiàn)方法
- .NET讀取Excel文件的三種方法的區(qū)別
- ASP.NET(C#)讀取Excel的文件內(nèi)容
- asp.net使用npoi讀取excel模板并導(dǎo)出下載詳解
- Asp.Net使用Npoi導(dǎo)入導(dǎo)出Excel的方法
- asp.net中EXCEL數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫的方法
- ASP.NET導(dǎo)出Excel打開時(shí)提示:與文件擴(kuò)展名指定文件不一致解決方法
- 直接在線預(yù)覽Word、Excel、TXT文件之ASP.NET
- .Net Core使用OpenXML導(dǎo)出、導(dǎo)入Excel
相關(guān)文章
在網(wǎng)絡(luò)編程中的通用方法 系統(tǒng)環(huán)境類 ASP.NET 專用屬性2009-06-06
2009-02-02
wireshark抓取本地回環(huán)數(shù)據(jù)包和取出數(shù)據(jù)的方法
這篇文章主要介紹了wireshark抓取本地回環(huán)數(shù)據(jù)包和取出數(shù)據(jù)的方法,需要的朋友可以參考下2014-02-02
這篇文章主要介紹了聊一聊Asp.net過濾器Filter那一些事，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-06-06
在.NET中掃描局域網(wǎng)服務(wù)的實(shí)現(xiàn)方法
下面小編就為大家分享一篇在.NET中掃描局域網(wǎng)服務(wù)的實(shí)現(xiàn)方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2018-01-01
ASP.Net MVC 布局頁、模板頁使用方法詳細(xì)介紹
這篇文章主要介紹了ASP.Net MVC 布局頁、模板頁使用方法詳細(xì)介紹,需要的朋友可以參考下2017-08-08
如何在.NET Core應(yīng)用中使用NHibernate詳解
NHibernate 是一個(gè)基于.Net 的針對(duì)關(guān)系型數(shù)據(jù)庫的對(duì)象持久化類庫。下面這篇文章主要給大家介紹了關(guān)于如何在.NET Core應(yīng)用中使用NHibernate的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友可以參考下2018-08-08
ASP.NET Core3.1 Ocelot負(fù)載均衡的實(shí)現(xiàn)
這篇文章主要介紹了ASP.NET Core3.1 Ocelot負(fù)載均衡的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
ASP.NET實(shí)現(xiàn)按拼音碼模糊查詢的方法
我們?cè)谧鰯?shù)據(jù)錄入或者查詢的時(shí)候，經(jīng)常需要實(shí)現(xiàn)按用戶輸入的拼音碼進(jìn)行數(shù)據(jù)的模糊查詢功能，本文為大家介紹ASP.NET如何實(shí)現(xiàn)按拼音碼模糊查詢，需要的朋友可以參考下2015-09-09
asp.net使用H5新特性實(shí)現(xiàn)異步上傳的示例
下面小編就為大家分享一篇asp.net使用H5新特性實(shí)現(xiàn)異步上傳的示例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2018-01-01