WEB转EXCEL详细说明
一、运用方法
主要运用namespace Microsoft.Office.Interop.Excel中的Applicatio
二、实现步骤
引用EXCEL:在COM中引用Microsoft Office 11.0 Object Libray和Microsoft EXCEL 11.0 Object Library。
代码:
/// <summary>
/// 创建Excel转Excel
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="grid">转的Dataset</param>
/// <param name="i">写入Exel的行坐标</param>
/// <param name="j">写入Exel的列坐标</param>
/// <param name="templetFilePath">保存URL</param>
/// <returns></returns>
/// <param name="pd"></param>
/// <returns></returns>
public bool TOExcel(string fileName, DataSet grid, int r, int c, string templetFilePath)
{
try
{
//创建一个Application对象并使其不可见
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false;
//创建一个WorkBook对象
workBook = app.Workbooks.Add(missing);
//得到WorkSheet对象
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
int rowCount = grid.Tables[0].Rows.Count + 1; //DataTable行数+GirdHead
int colCount = grid.Tables[0].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.Tables[0].Rows[0].ItemArray[k].ToString();
}
else
{
arr[j, k] = grid.Tables[0].Rows[j - 1].ItemArray[k].ToString();
}
}
}
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[r, c]; //写入Exel的坐标
range = range.get_Resize(rowCount, colCount);
range.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();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = 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();//强制代码垃圾回收
return true;
}
catch { return false; }
}
/// <summary>
/// 通过模板转Excel
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="grid">转的Dataset</param>
/// <param name="i">写入Exel的行坐标</param>
/// <param name="j">写入Exel的列坐标</param>
/// <param name="templetFilePath">保存URL</param>
/// <returns></returns>
/// <param name="pd"></param>
/// <returns></returns>
public bool MTOExcel(string fileName, DataSet grid, int r, int c, string templetFilePath)
{
try
{
//创建一个Application对象并使其不可见
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false;
//打开模板文件,得到WorkBook对象
workBook = app.Workbooks.Open(templetFilePath + fileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
int rowCount = grid.Tables[0].Rows.Count + 1; //DataTable行数+GirdHead
int colCount = grid.Tables[0].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.Tables[0].Rows[0].ItemArray[k].ToString();
}
else
{
arr[j, k] = grid.Tables[0].Rows[j - 1].ItemArray[k].ToString();
}
}
}
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[r, c]; //写入Exel的坐标
range = range.get_Resize(rowCount, colCount);
range.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();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = 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();//强制代码垃圾回收
return true;
}
catch { return false; }
}