.net 导出复杂格式的EXCEL
[csharp]
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">要导出的DataTable</param>
public void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
// lblMsg.Text = "无法创建Excel对象,可能您的机子未安装Excel";
lblMsg.Text = GetLocalResourceObject("noexcel").ToString();
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//表头
range = worksheet.get_Range("A1", "H1");
range.Merge(Missing.Value);
range.Font.Bold = true;
range.Font.Size = 16;
range.Value2 = "金润天公司原材料入库(报验)单";
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//第2行
worksheet.Cells[2, 1] = "供货方:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
range = worksheet.get_Range("B2", "D2");
range.Merge(Missing.Value);
range.Font.Size = 10;
range.Value2 = DropDownList2.Text;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
worksheet.Cells[2, 5] = "日期:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 5];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[2, 6] = add_timetb.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 6];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
worksheet.Cells[2, 7] = "单号:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2,7];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[2, 8] = ins_idtb.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 8];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
//第3行
worksheet.Cells[3, 1] = "合同号:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[3, 1];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
range = worksheet.get_Range("B3", "D3");
range.Merge(Missing.Value);
range.Font.Size = 10;
range.Value2 = c_id_lb.Text;//Excel单元格赋值
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
//第4行
worksheet.Cells[4, 1] = "订单号:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[4, 1];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
range = worksheet.get_Range("B4", "D4");
range.Merge(Missing.Value);
range.Font.Size = 10;
range.Value2 = dingdan_numtb.Text;//Excel单元格赋值
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
//第5行
worksheet.Cells[5, 1] = "入库明细:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[5, 1];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[6, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[6, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.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 + 7, i + 1] = dt.Rows[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 7, i + 1];
range.Font.Size = 10;//字体大小
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
for (int i = 0; i < 8; i++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(7 + totalCount), i + 1];
range.Font.Size = 10;//字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
}
for (int i = 0; i < 8; i++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(8 + totalCount), i + 1];
range.Font.Size = 10;//字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
}
//第7+totalCount行
worksheet.Cells[(8 + totalCount), 1] = "合计(RMB/元):"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(8 + totalCount), 1];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
worksheet.Cells[(8 + totalCount), 2] = heji_lb.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(8 + totalCount), 2];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
worksheet.Cells[(8 + totalCount), 3] = "税票:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(8 + totalCount), 3];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[(8 + totalCount), 4] = shuilvTB.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(8 + totalCount), 4];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
//第9+totalCount行
worksheet.Cells[(9 + totalCount), 1] = "质检:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(9 + totalCount), 1];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
worksheet.Cells[(9 + totalCount), 2] = zhijiantb.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(9 + totalCount), 2];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
worksheet.Cells[(9 + totalCount), 3] = "库房:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(9 + totalCount), 3];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[(9 + totalCount), 4] = kufangtb.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(9 + totalCount), 4];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
worksheet.Cells[(9 + totalCount), 5] = "采购:"; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(9 + totalCount), 5];
range.Font.Bold = true;
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[(9 + totalCount), 6] = shengchantb.Text; //Excel单元格赋值
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(9 + totalCount), 6];
range.Font.Size = 10;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.EntireColumn.AutoFit();
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + ins_idtb.Text + ".xls");
}
catch (Exception ex)
{
//lblMsg.Text = "导出文件时出错,文件可能正被打开!\n" + ex.Message;
lblMsg.Text = GetLocalResourceObject("error").ToString() + "\n" + ex.Message;
}
workbooks.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
//提供下载
//BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"
// , System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000);
string fileName = "入库单-" + ins_idtb.Text;//客户端保存的文件名
string filePath = Server.MapPath("../XMLFiles/EduceWordFiles/" + ins_idtb.Text + ".xls");//路径
FileInfo fileInfo = new FileInfo(filePath);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls") + "");
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
}
上一篇: 防止恶意解析,禁止通过IP直接访问网站
下一篇: Python pycharm的快捷键汇总
推荐阅读
-
Yii框架使用PHPExcel导出Excel文件的方法分析【改进版】
-
使用python将大量数据导出到Excel中的小技巧分享
-
vue中导出Excel表格的实现代码
-
asp.net 利用NPOI导出Excel通用类的方法
-
建议收藏:.net core 使用EPPlus导入导出Excel详细案例,精心整理源码已更新至开源模板
-
用ASP将数据库中的数据直接导出到EXCEL表中
-
asp.net页面中如何获取Excel表的内容
-
ASP.NET技巧:数据岛出到Excel最为简易的方法
-
Yii中使用PHPExcel导出Excel的方法
-
利用Asp.Net Core的MiddleWare思想如何处理复杂业务流程详解