C#中使用Nopi导入EXCEL表格,只是更改了一下表头,再导出时excel表格的大小变大好几倍? 80C

比如:
导入一个130k的表格,在c#中将其转为datatable后,更改了一下表头和某些少量数据。但并未增加数据!然后再将datatable转为excel表格并导出时。新表格的大小为890k.
注意:1.检查过没有隐藏的对象。
2.表格的单元格大小前后一致。
恳请大佬帮忙解决一下:

  /// 导出Excel 
        /// </summary>
        /// <param name="SourceTable">导出的数据源</param>
        /// <param name="page">WEB页面对象,默认传Page,只可WEB后台调用</param>
        /// <param name="type">WEB页面值,如果是素金则另作处理</param>
        /// <returns></returns>
        public void ExportExcel(DataTable ds, DataTable dtt, Page page, string type)
        {
            DataTable dt = ds;
            string basePath = page.Server.MapPath(".");
            //如果目录不存在,则创建目录 
            if (!Directory.Exists(basePath))
            {
                Directory.CreateDirectory(basePath);
            }
            string filePath = string.Empty;
            string fileName = string.Empty;

            //filePath = page.Server.MapPath(".") + "\\" + fileName + ".xls";

            // filePath = page.Server.MapPath(".") + "\\" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";


            MemoryStream ms;
            if (type == "素金")
            {
                fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
                ms = RenderDataTableToExcelSujin(dt, page) as MemoryStream;
            }
            else
            {
                if (ds.Rows.Count > 0)
                {
                    if (ds.Rows[0]["采购订单号"].ToString().Trim().Length != 0)
                    {
                        fileName = ds.Rows[0]["采购订单号"].ToString().Trim();
                    }
                    else
                    {
                        fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
                    }
                }
                else
                {
                    fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
                }
                ms = RenderDataTableToExcel(dt, dtt, page) as MemoryStream;
            }

            FileStream fs;
            //FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            try
            {
                filePath = page.Server.MapPath(".") + "\\" + fileName + ".xls";
                fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);

            }
            catch (Exception)
            {

                fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
                filePath = page.Server.MapPath(".") + "\\" + fileName + ".xls";
                fs = new FileStream(page.Server.MapPath(".") + "\\" + fileName + ".xls", FileMode.Create, FileAccess.Write);
            }
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
            data = null;
            ms = null;
            fs = null;
            page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            page.Response.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312");
            page.Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls");
            page.Response.ContentType = "Application/excel";
            if (page.Response.IsClientConnected)
            {
                page.Response.WriteFile(filePath);
                page.Response.Flush();
            }
            //Response.WriteFile(filePath);
            //Response.Flush();
            try
            {
                //删除导出的服务器文件
                File.Delete(filePath);
            }
            catch (Exception)
            {
            }
            page.Response.Clear();
        }

                 private Stream RenderDataTableToExcelSujin(DataTable SourceTable, Page page)
        {
            MemoryStream ms = new MemoryStream();
            HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            //加标题  
            IRow headerRow2 = sheet.CreateRow(0);
            headerRow2.Height = 500;

            //加了两行空行
            IRow dataRow1 = sheet.CreateRow(1);
            //列高2000
            dataRow1.Height = 500;
            dataRow1.Sheet.SetColumnWidth(0, 50 * 60);
            dataRow1.Sheet.SetColumnWidth(1, 50 * 60);
            dataRow1.Sheet.SetColumnWidth(2, 50 * 60);
            dataRow1.Sheet.SetColumnWidth(3, 50 * 95);

            IRow dataRow2 = sheet.CreateRow(2);
            //列高2000
            dataRow2.Height = 500;
            dataRow2.Sheet.SetColumnWidth(0, 50 * 60);
            dataRow2.Sheet.SetColumnWidth(1, 50 * 60);
            dataRow2.Sheet.SetColumnWidth(2, 50 * 60);
            dataRow2.Sheet.SetColumnWidth(3, 50 * 95);


            //添加Excel的表头
            for (int i = 0; i < SourceTable.Columns.Count; i++)
            {
                headerRow2.CreateCell(i).SetCellValue(SourceTable.Columns[i].ColumnName);
            }
            int rowIndex = 3;
            Regex regExp = new Regex(@"^(-?\d+)(\.\d+)?$");
            List<string> lstColumnname = new List<string>();
            foreach (DataColumn item in SourceTable.Columns)
            {
                for (int i = 0; i < SourceTable.Rows.Count; i++)
                {
                    if (SourceTable.Rows[i][item.ColumnName].ToString().Trim().Length > 0)
                    {
                        if (!regExp.IsMatch(SourceTable.Rows[i][item.ColumnName].ToString().Trim()))
                        {
                            lstColumnname.Add(item.ColumnName);
                            break;
                        }
                    }
                }
            }
            foreach (DataRow row in SourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                //列高2000
                dataRow.Height = 500;
                dataRow.Sheet.SetColumnWidth(0, 50 * 60);
                dataRow.Sheet.SetColumnWidth(1, 50 * 60);
                dataRow.Sheet.SetColumnWidth(2, 50 * 60);
                dataRow.Sheet.SetColumnWidth(3, 50 * 95);
                ////素金特殊要求:除表头从第三行开始绑定
                //if (rowIndex <= 2)
                //{
                //    rowIndex++;
                //    continue;
                //}
                for (int i = 0; i < SourceTable.Columns.Count; i++)
                {
                    try
                    {
                        if (row[i].ToString().Trim().Length != 0)
                        {
                            if (lstColumnname.Contains(SourceTable.Columns[i].ColumnName))
                                dataRow.CreateCell(i).SetCellValue(@row[i].ToString());
                            else
                            {
                                string aaa = SourceTable.Columns[i].ColumnName;
                                if (row[i].ToString().Contains("."))
                                {
                                    //有小数 转double
                                    dataRow.CreateCell(i).SetCellValue(@Convert.ToDouble((row[i].ToString() == "" ? "0" : row[i].ToString())));
                                }
                                else
                                {
                                    //无小数 转int
                                    dataRow.CreateCell(i).SetCellValue(@Convert.ToInt32(row[i].ToString()));
                                }
                            }
                        }
                        else dataRow.CreateCell(i).SetCellValue(@row[i].ToString());
                    }
                    catch (Exception)
                    {
                        dataRow.CreateCell(i).SetCellValue(@row[i].ToString());
                    }
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow2 = null;
            workbook = null;
            return ms;
        }

1个回答

FileStream fs = new FileStream(“demo.xls”, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流

POIFSFileSystem ps=new POIFSFileSystem(fs);//需using NPOI.POIFS.FileSystem;
IWorkbook workbook = new HSSFWorkbook(ps);
ISheet sheet = workbook.GetSheetAt(0);//获取工作表
IRow row = sheet.GetRow(0); //得到表头
FileStream fout = new FileStream(“demo.xls”, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流
row = sheet.CreateRow((sheet.LastRowNum + 1));//在工作表中添加一行

ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(“测试数据”);//赋值

fout.Flush();
workbook.Write(fout);//写入文件
workbook = null;
fout.Close();

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐