public void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null)
{
return;
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入标题
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;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs("C:\\datatable" + "" + ".xls");
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
xlApp.Quit();
GC.Collect();//强行销毁
//这个是从服务器中下载文件,(请参考我另外一个文章)
//参考网址http://www.cnblogs.com/ghostljj/archive/2007/01/24/629293.html
//BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"
// , System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000);
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
dt.Columns.Remove("id");
dt.Columns.Remove("year");
dt.Columns.Remove("month");
dt.Columns.Remove("jobflowid");
dt.AcceptChanges();
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行
range.Merge(0);
worksheet.Cells[1, 1] = this.biaoti.InnerText;
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号"; //加入序号列
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];
}
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (i == 0)//序号列宽度设为自动调整
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0) //增加序号
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString();
}
else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列
{
worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日");
}
else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
range.NumberFormat = "#,##0.00";
}
else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列
{
if (dt.Rows[r][c - 1].ToString() == "1")
{
worksheet.Cells[r + 3, c + 1] = "已支付";
}
else
{
worksheet.Cells[r + 3, c + 1] = "未支付";
}
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
//设置样式
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
//设置合计那一行
range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString());
range.Font.ColorIndex = 41;
//range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Font.Size = 10;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
range.EntireColumn.AutoFit(); //自动调整列宽
worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:"; //合计那一行的第一列
if (dt.Rows.Count == 0)
{
worksheet.Cells[3 + dt.Rows.Count, 7] = 0;
}
else
{
worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")";
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
System.Windows.Forms.Application.DoEvents();
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "O1"); //获取表格中第一行
range.Merge(0); //合并第一行
worksheet.Cells[1, 1] = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号";
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据
}
//设置标题的样式
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
if (i == 0) //序号列宽度自动
{
range.EntireColumn.AutoFit();
}
else
{
//设置单元格的宽度，如果小于9就设置为9，如果大于。则设置为自动
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0)
{
if (r == dt.Rows.Count - 1)
{
worksheet.Cells[r + 3, c + 1] = "";
}
else
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); //得到序号
}
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
}
else if (c == 1)
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0];
range.NumberFormat = "#,##0.00";
}
//设置样式
range.Font.Size = 9; //字体大小
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
if (r == dt.Rows.Count - 1)
{
range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString());
range.Merge(0);
range.Value = "合计";
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
range.EntireColumn.AutoFit();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}