.net 导出复杂格式的EXCEL

首先要添加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了注意网页编码 中文乱码问题 这个就自己解决吧哈哈,下面这个是我项目中用到的一个小例子 自己看着改吧
    /// <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();




    }

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值