欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.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.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();  

  

  

  

  

}