（一）直接從Gridview中導(dǎo)出數(shù)據(jù)到Excel：
protected void btnExport_Click(object sender, EventArgs e)
{
string sql="";
DataTable MyTable = DB.getdatetable(sql);
grvExport.DataSource = MyTable;
grvExport.DataBind();
Response.Clear();
Response.Charset = "GB2312";
Response.Write("");
Response.AddHeader("content-disposition", "attachment; filename=WWInvReport.xls");
Response.ContentType = "application/vnd.ms-excel";
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
grvExport.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
hw.Dispose();
tw.Dispose();
MyTable.Dispose();
adp.Dispose();
cnn.Dispose();
GC.Collect();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
下面的過(guò)載函數(shù)一定要有，否則會(huì)出錯(cuò)；注意GridView若是分頁(yè)或者排序的先將其設(shè)置為false;
若不希望有列的數(shù)字轉(zhuǎn)化為科學(xué)計(jì)數(shù)法在DataBound中設(shè)置
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
（二）用拼寫HTML的方法直接輸出：
public static void DownLoad_SQLtoExcel_2(Page page, DataView dv, string fileName)
{
StringBuilder sb = new StringBuilder();
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
sb.Append("
| ") .Append(" " + dv.Table.Columns[i].ToString()) .Append(" | ");
| " + cells + " | ");
（三）分Sheet導(dǎo)出
public static void Excel(DataSet ds, string file_path_name, string file_name)
{
//定義動(dòng)態(tài)數(shù)組，以便存放dataset中每個(gè)表的記錄數(shù)
long[] rows = new long[ds.Tables.Count];
string saveFileName = file_path_name;
Application xlApp = new Excel.ApplicationClass();
_Workbook workbook=xlApp.Workbooks.Add(true);
_Worksheet worksheet =null; //取得sheet1
//查看本機(jī)是否裝有OFFICE軟件
if (xlApp == null)
{
return;
}
//tc是table_count,用來(lái)遍歷每個(gè)表
for (int tc = 0; tc < ds.Tables.Count; tc++)
{
object missing1 = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing1, missing1, missing1, missing1);//添加一個(gè)sheet
worksheet.Name = ds.Tables[tc].TableName; //將DataSet表名來(lái)作為輸出Excel中Sheet的名字
rows[tc] = ds.Tables[tc].Rows.Count;
//定義動(dòng)態(tài)數(shù)姐存放所有需要匯出的數(shù)據(jù)
string[,] datas = new string[ds.Tables[tc].Rows.Count + 1, ds.Tables[tc].Columns.Count];
for (int i = 0; i < ds.Tables[tc].Columns.Count; i++) //寫入標(biāo)題
{
datas[0, i] = ds.Tables[tc].Columns[i].Caption;
}
//標(biāo)題的記錄區(qū)域range_title
Excel.Range range_title = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, ds.Tables[tc].Columns.Count]);
range_title.Interior.ColorIndex = 15; //15代表灰色
range_title.Font.Bold = true;
range_title.Font.Size = 9;
//r用來(lái)記錄table的行數(shù)，j用來(lái)記錄table的列數(shù)
int r = 0;
for (r = 0; r < ds.Tables[tc].Rows.Count; r++)
{
for (int j = 0; j < ds.Tables[tc].Columns.Count; j++)
{
if (ds.Tables[tc].Columns[j].DataType == typeof(string) || ds.Tables[tc].Columns[j].DataType == typeof(int) || ds.Tables[tc].Columns[j].DataType == typeof(Decimal) || ds.Tables[tc].Columns[j].DataType == typeof(DateTime))
{
//依次取每行每列的數(shù)據(jù)
object obj = ds.Tables[tc].Rows[r][ds.Tables[tc].Columns[j].ColumnName];
datas[r + 1, j] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加單引號(hào)是為了防止自動(dòng)轉(zhuǎn)化格式
}
}
}
//定義整個(gè)區(qū)域包括標(biāo)題和數(shù)據(jù)
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[ds.Tables[tc].Rows.Count + 1, ds.Tables[tc].Columns.Count]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)。
//標(biāo)題的記錄區(qū)域range_data
Excel.Range range_data = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds.Tables[tc].Rows.Count + 1, ds.Tables[tc].Columns.Count]);
range_data.Font.Size = 9;
range_data.RowHeight = 14.25;
range_data.Borders.LineStyle = 1;
range_data.HorizontalAlignment = 1;
//--合併單元格的操作--
//數(shù)組操作從0開始,ExcelCells從1開始
int n = 2; //Excel從第2行開始，因?yàn)榈谝恍惺菢?biāo)題
for (int m = 2; m <= ds.Tables[tc].Rows.Count + 1; m++)
{
if (m
if (ds.Tables[tc].Rows[m - 2][0].ToString() == ds.Tables[tc].Rows[m - 1][0].ToString())//DataTable是從第０行開始
{
xlApp.Cells[m + 1, 1] = "";
}
else
{
worksheet.get_Range((Excel.Range)worksheet.Cells[n, 1], (Excel.Range)worksheet.Cells[m, 1]).Merge(0);//合并單元格
n = m + 1;
}
}
else
{
worksheet.get_Range((Excel.Range)worksheet.Cells[n, 1], (Excel.Range)worksheet.Cells[m, 1]).Merge(0);
}
}
}
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
workbook.Close(false, null, null);
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.WriteFile(file_path_name);
response.Charset = "utf-8";
response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
response.ContentType = "application/ms-excel";
string httpHeader = "attachment;filename=" + file_name + ".xls";
response.AppendHeader("Content-Disposition", httpHeader);
response.Flush();
System.IO.File.Delete(saveFileName);//刪除臨時(shí)文件
xlApp.Quit();
GC.Collect();//強(qiáng)行銷毀
response.End();
}
這種方法用的時(shí)候，需要在服務(wù)器上設(shè)置元件服務(wù)的權(quán)限，另外一定要有workbook.Close(false, null, null); 這句，否則的話每次會(huì)在服務(wù)器上開啟一個(gè)Excel進(jìn)程，多次之后就會(huì)報(bào)錯(cuò)；
重慶分公司
重慶分公司
