Question

怎么用C#把数据库中的数据导出到EXCEL

谢谢~

Answer 1

下面的函数作用，将DataTable导出到EXCEL文件：
private void DataTabletoExcel(System.Data.DataTable tmpDataTable,string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;

Excel.Application xlApp = new Excel.ApplicationClass();

xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;

Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

//将DataTable的列名导入Excel表第一行
foreach(DataColumn dc in tmpDataTable.Columns)
{
columnIndex ++;
xlApp.Cells[rowIndex,columnIndex] = dc.ColumnName;
}

//将DataTable中的数据导入Excel中
for(int i = 0;i<rowNum; i++)
{
rowIndex ++;
columnIndex = 0;
for (int j = 0;j<columnNum; j++)
{
columnIndex ++;
xlApp.Cells[rowIndex,columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
xlBook.SaveCopyAs(strFileName + ".xls");
}

Answer 2

Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename="+filename);
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-"+type+"";
this.EnableViewState = false;
StringWriter stringWriter=new StringWriter();
HtmlTextWriter htmlWriter=new HtmlTextWriter(stringWriter);
DataGrid excel=new DataGrid();
System.Web.UI.WebControls.TableItemStyle aStyle=new TableItemStyle();
System.Web.UI.WebControls.TableItemStyle hStyle=new TableItemStyle();
System.Web.UI.WebControls.TableItemStyle iStyle=new TableItemStyle();
aStyle.BackColor=System.Drawing.Color.LightGray;
hStyle.BackColor=System.Drawing.Color.LightGray;
hStyle.Font.Bold=true;
hStyle.HorizontalAlign=System.Web.UI.WebControls.HorizontalAlign.Center;
iStyle.HorizontalAlign=System.Web.UI.WebControls.HorizontalAlign.Center;
iStyle.VerticalAlign=System.Web.UI.WebControls.VerticalAlign.Bottom;
excel.AlternatingItemStyle.MergeWith(aStyle);
excel.HeaderStyle.MergeWith(iStyle);
excel.GridLines=GridLines.Both;
excel.HeaderStyle.Font.Bold=true;
excel.DataSource=dt.DefaultView;
excel.DataBind();
excel.RenderControl(htmlWriter);
Response.Write(stringWriter.ToString());
Response.End();

方法调用：
this.OutToWordOrExcel("CustomerRepairRecord.xls","excel",dt);