.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了注意网页编码 中文乱码问题 这个就自己解决吧哈哈，下面这个是我项目中用到的一个小例子 自己看着改吧

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

  

  

  

  

}  

 

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复