.net 导出复杂格式的EXCEL

程序员文章站 2022-04-19 17:37:59

首先要添加EXCEl引用在网上搜一下应该就能找到添加的方法，注意要跟服务器中的EXCEl版本一直然后在cs中添加using Microsoft.Office.Interop.Exc...

首先要添加EXCEl引用在网上搜一下应该就能找到添加的方法，注意要跟服务器中的EXCEl版本一直然后在cs中添加using Microsoft.Office.Interop.Excel;using Microsoft.Office.Core;using System.Reflection;using System.IO;可能还有别的，自己看VS提示加吧然后在Web.config中下加入计算机登陆的用户名密码，这是给予程序权限调用EXCEL DLL下面就是方法了添加一个DataTable就能导出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.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;//字体大小

//加边框

}

for (int i = 0; i < 8; i++)

{

range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[(8 + totalCount), i + 1];

range.Font.Size = 10;//字体大小

//加边框

}

//第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();

}

上一篇： [日常] nginx反代websocket

下一篇：高血压吃十二种水果帮你有效的稳定血压

.net 导出复杂格式的EXCEL

C#导出Excel的方法

ASP.NET之Excel下载模板、导入、导出操作

浅谈c#.net中巧用ToString()将日期转成想要的格式

php不使用插件导出excel的简单方法

格式导致的Excel导入sql出现异常的解决方法

php导出csv格式数据并将数字转换成文本的思路以及代码分享

利用pandas将numpy数组导出生成excel的实例

Excel表格怎么批量导出图片将文件中的图片导出到文件夹中

Excel2007如何将编辑好的表格以图片的格式保存且想怎么移就怎么移

Excel用自定义格式配合条件格式打出名副其实的平方米(M2)符号