.net 导出复杂格式的EXCEL

来源：互联网发布：秦国为何灭亡知乎编辑：程序博客网时间：2024/06/13 23:32
首先要添加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();    }