今天朋友问我有没有做过将多个DataGrid中的内容导出到一个Excel文件中,每个DataGrid放在一个Sheet中,在此之前,我只用JS导出过Excel文件,没有用Excel组件做过,正好今天比较空闲,就从网上查了资料(http://www.wsoft.net/Index/Catalog53/225.aspx),写得非常详细,就直接拿来用了
只是有一点儿不太符合要求,需求是“多个DataGrid导入到同一个Excel文件中,且每个DataGrid放在一个Sheet中”。于是自己动手改了下,代码如下:(Excel文件中默认有三个Sheet,超出三个需要创建,可能我这种方法比较笨,哪位强人有好的方法不防提供下)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/// <summary>
/// 将DataGrid中数据导出至Excel,生成标准的Excel文件
/// (注:此方法只能生成在服务器上,如果需要生成在客户端,可以先在服务器上生成文件然后下载到客户端)
/// </summary>
/// <param name="al_grid">DataGrid控件列表</param>
/// <param name="fileName">导出文件名</param>
protected void ExportToExcel(ArrayList al_grid,string fileName)
{
int grid_count = al_grid.Count;
string templetFilePath;
templetFilePath = "e:\\test1\\";//这里指定要生成的文件存放位置
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app;
Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet[] workSheet = new Microsoft.Office.Interop.Excel.Worksheet[grid_count];
Microsoft.Office.Interop.Excel.Range[] range = new Microsoft.Office.Interop.Excel.Range[grid_count];
//创建一个Application对象并使其不可见
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible=false;
//创建一个WorkBook对象
workBook = app.Workbooks.Add(missing);
for(int i=0;i<grid_count;++i)
{
System.Web.UI.WebControls.DataGrid grid = (System.Web.UI.WebControls.DataGrid)al_grid[i];
//得到WorkSheet对象
if(i >= 3)
{
workSheet[i] = (Microsoft.Office.Interop.Excel.Worksheet)workBook.
Worksheets.Add(Type.Missing, workBook.ActiveSheet,
Type.Missing, Type.Missing);
}
else
{
workSheet[i] = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(i+1);
}
int rowCount = grid.Items.Count + 1; //DataTable行数+GirdHead
int colCount = grid.Columns.Count; //DataTable列数
//利用二维数组批量写入
string[,] arr = new string[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
if (j == 0)
{
arr[j, k] = grid.Columns[k].HeaderText;
}
else
{
arr[j, k] = grid.Items[j - 1].Cells[k].Text.ToString();
}
}
}
range[i] = (Microsoft.Office.Interop.Excel.Range)workSheet[i].Cells[1, 1]; //写入Exel的坐标
range[i] = range[i].get_Resize(rowCount, colCount);
range[i].Value2 = arr;
}
workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing,missing,missing,missing);
if (workBook.Saved)
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
}
for(int i=0;i<grid_count;++i)
{
if (range[i] != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range[i]);
range[i] = null;
}
if (workSheet[i] != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet[i]);
workSheet[i] = null;
}
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();//强制代码垃圾回收
//下载文件
DownLoadFile(templetFilePath,fileName);
}
/// <summary>
/// 下载服务器文件到客户端
/// </summary>
/// <param name="_FilePath">文件路径</param>
/// <param name="_FileName">文件名</param>
/// <returns>返回 bool型</returns>
private bool DownLoadFile(string _FilePath,string _FileName)
{
try
{
System.IO.FileStream fs = System.IO.File.OpenRead(_FilePath+_FileName);
byte[] FileData = new byte[fs.Length];
fs.Read(FileData, 0, (int)fs.Length);
Response.Clear();
Response.AddHeader("Content-Type", "application/ms-excel");
string FileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_FileName));
Response.AddHeader("Content-Disposition", "inline;filename=" + System.Convert.ToChar(34) + FileName + System.Convert.ToChar(34));
Response.AddHeader("Content-Length", fs.Length.ToString());
Response.BinaryWrite(FileData);
fs.Close();
//删除服务器临时文件
System.IO.File.Delete(_FilePath+_FileName);
Response.Flush();
Response.End();
return true;
}
catch(Exception ex)
{
ex.Message.ToString();
return false;
}
}
//生成Excel
private void btnExcel_Click(object sender, System.EventArgs e)
{
ArrayList al = new ArrayList();
al.Add(dgInfo1);//把所有datagrid放入arraylist
al.Add(dgInfo2);
al.Add(dgInfo3);
al.Add(dgInfo4);
al.Add(dgInfo5);
this.ExportToExcel(al,"userlist.xls");
}
当然,要在项目中增加Microsoft.Office.Interop.Excel组件:)