导出Excel

#region 導出Excel
private void outPutExcel(System.Data.DataTable dt, System.Data.DataTable dt2)
{
if (dt == null) return;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
//clsLog.m_CreateErrorLog("无法创建Excel对象，可能计算机未安装Excel", "", "");
return;
}
//創建Excel對象
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Worksheet worksheet = null;
for (int sheetcount = 0; sheetcount < 2; sheetcount++)//循环根据自己需要的sheet的数目这里是两个
{
if (worksheet == null)
{
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, worksheet, 1, Type.Missing);
}
Excel.Range range = null;
if (sheetcount == 0)
{
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Name = "明細資料一";//第一个sheet在Excel中显示的名称
////写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.ColumnWidth = 4.63;//设置列宽
range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}

//写入内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
}
else if (sheetcount == 1)
{
long totalCount = dt2.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Name = "建議解決方案";
////写入标题
for (int i = 0; i < dt2.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt2.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.ColumnWidth = 14.63;//设置列宽
range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt2.Rows.Count; r++)
{
for (int i = 0; i < dt2.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt2.Rows[r][i];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
//System.Windows.Forms.Application.DoEvents();
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt2.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
}
}

//下面是将Excel存储在服务器上指定的路径与存储的名称
try
{
string tPath = System.AppDomain.CurrentDomain.BaseDirectory;
if (!Directory.Exists(tPath + "Excel"))
{
Directory.CreateDirectory(tPath + "Excel");
}
workbook.SaveCopyAs(tPath + "Excel" + "\\" + System.DateTime.Today.ToString("yyyyMMdd") + pCompany + "测试.xls");
}
//writeLog("生成Exce附件成功！");//此处是我的一个方法，记录log讯息的
}
catch(Exception ex)
{
string strMsgErr = "生成Excel附件過程中出現異常，詳細信息如：" + ex.ToString();
//writeLog(strMsgErr);//这个也是写log档的
}
//我们导出Excel的时候会在进程中调用Excel进程，导出之后必须强制杀掉进程
try
{
if (xlApp != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
catch (Exception ex)
{
Console.WriteLine("Delete Excel Process Error:" + ex.Message);
}
}
#endregion