DotNet 操纵Excel总结之四
以下是我在项目中用到的一段方法，
/// <summary>
/// 将DataTable数据写入Excel文件（套用模板并分页）,并输出统计时间
/// </summary>
/// <param name="dt">结果集</param>
/// <param name="rows">写入总行数</param>
/// <param name="column">写入总列数</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前缀名</param>
/// <param name="stime">统计开始时间</param>
/// <param name="etime">统计结束时间</param>
public void DataTableToExcelThree(DataTable dt, int rows, int column, int top, int left, string sheetPrefixName,string stime,string etime)
{
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet";
//创建一个Application对象并使其不可见
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false;
;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
Excel.Range range1 = workSheet.get_Range(workSheet.Cells[2, 1], workSheet.Cells[2, 4]);
DateTime dtime = Convert.ToDateTime(stime);
stime = dtime.ToString("yyyy年MM月dd日");
dtime = Convert.ToDateTime(etime);
etime = dtime.ToString("yyyy年MM月dd日");
string temp = "统计时间：" + stime + " 到 " + etime;
range1.Value2 = temp;
Excel.Range range = workSheet.get_Range(workSheet.Cells[top, left], workSheet.Cells[top + rows, column]);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.Font.Size = 10;
range.Font.Name = "Arial";
range = workSheet.get_Range(workSheet.Cells[top, left], workSheet.Cells[top + rows - 1, column]);
object[,] strTmp = new object[rows, column];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
strTmp[i, j] = dt.Rows[i][j];
}
}
range.Value2 = strTmp;
range = workSheet.get_Range(workSheet.Cells[top, left], workSheet.Cells[top + rows, column]);
range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
//输出Excel文件并退出
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
如果要进行垃圾回收，可以在调用这段方法结束前写一句代码：
GC.Collect();
但我发现在个别服务器上，可以直接关闭Excel.exe进程，但有些服务器就是关闭不了！
