• DataTable Excel


    这里是使用Microsoft.Office.Interop.Excel类进行Excel表格操作,核心代码就这些了。麻烦的是服务器上的一些关于access的权限配置。             

       string file = System.AppDomain.CurrentDomain.BaseDirectory + "对账单\\" + filename;
    
                    if (ExcelHelper.DataTableToCSV(dt, file + System.Web.HttpContext.Current.Session["us_Name"] + ".csv", new string[] { "rowId" }))
                    {
                        HttpResponse resp;
                        resp = System.Web.HttpContext.Current.Response;
                        resp.Clear();
                        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                        resp.ContentType = "application/vnd.ms-excel";
                        resp.Charset = "GB2312";
    
                        resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8) + ".csv");
                        resp.WriteFile(file + System.Web.HttpContext.Current.Session["us_Name"] + ".csv");
                    }
    
            public static bool DataTableToCSV(System.Data.DataTable excelTable, string filePath,string[] removeFileds)
            {
    
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
                app.Visible = false;
    
                Workbooks workBooks = app.Workbooks; //创建Excel工作簿
                Workbook wBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet);
    
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
    
                foreach (string item in removeFileds)
                {
                    if (excelTable.Columns.Contains(item))
                    {
                        excelTable.Columns.Remove(item);
                    }
                }
    
                try
                {
    
    
                    if (excelTable.Rows.Count > 0)
                    {
                        int row = 0;
                        row = excelTable.Rows.Count;
                        int col = excelTable.Columns.Count;
    
                        //关键性代码,直接关系到性能问题
                Array arr = Array.CreateInstance(typeof(String), row, col);
    
                        for (int i = 0; i < row; i++)
                        {
    
                            for (int j = 0; j < col; j++)
                            {
    
                                arr.SetValue(excelTable.Rows[i][j].ToString(), i, j);
    
                            }
    
                        }
    
                        Microsoft.Office.Interop.Excel.Range range = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[row + 1, col]);
                        range.Value2 = arr; 
                    }
    
                    int size = excelTable.Columns.Count;
                    for (int k = 0; k < size; k++)
                    {
    
                        wSheet.Cells[1, 1 + k] = excelTable.Columns[k].ColumnName;
                        Range xlsRang = (Range)wSheet.Cells[1, 1 + k];
                        xlsRang.EntireColumn.AutoFit();
                    }
    
                    //设置禁止弹出保存和覆盖的询问提示框   
                    app.DisplayAlerts = false;
                    app.AlertBeforeOverwriting = false;
                    //保存工作簿   
    
                    wBook.Save();
                    wBook.SaveCopyAs(filePath); //自定义保存的文件名称
    
                }
                catch (Exception err)
                {
                    return false;
                }
                finally
                {
    
                    workBooks.Close(); //关闭工作簿
                    if (app != null)
                    {
                        app.Workbooks.Close();
                        app.Quit(); //退出Excel
                        int intGenerate = System.GC.GetGeneration(app);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //将XLSAPP从内存托管堆中移除
                        app = null;
                        System.GC.Collect(intGenerate);
                    }
                    GC.Collect(); //强行退出Excel
                    app = null;
    
                }
    
                return true;
            }
    
  • 相关阅读:
    我的腾讯云服务器被暴力破解了
    asp.net 动态运用webservice并设置超时时间
    Elasticsearch7.4 spring boot 使用 RestHighLevelClient实现searchAfter分页
    华为OD机试题
    获取某日期后一周、一月、一年的日期 php
    PHP 读取文件夹(比如某共享文件夹)中的图片并显示
    php 获取某文件夹(比如共享文件夹)下图片并下载并压缩成zip
    数据库集群简单概念
    java数据流
    SpringBoot使用切面+注解实现对所有请求的日志记录到数据库中
  • 原文地址:https://www.cnblogs.com/diulela/p/2285833.html
Copyright © 2020-2023  润新知