您的位置: 首页  >  IT文章  >  ASP.NET  >  asp.net如何将DataTable分多个Sheet导出到Excel

asp.net如何将DataTable分多个Sheet导出到Excel

分类: IT文章 • 2022-07-08 00:35:11
asp.net怎么将DataTable分多个Sheet导出到Excel
怎么将DataTable按照部门分类，部门分别添加到sheet中并导出，请问要怎么实现

参考了一个帖子http://blog.csdn.net/mh942408056/article/details/7298325
但是会报错
asp.net如何将DataTable分多个Sheet导出到Excel

各路大神要怎么实现这种功能啊，

------解决思路----------------------


/// <summary>

        /// 导出Excel 把一个dataset的多个datatable导入到一个excel的多个sheet中

        /// </summary>

        /// <param name="ds">DataSet</param>

        /// <param name="tableNames">ds里每个表的表名</param>

        /// <param name="strExcelFileName">导出Excel名称(YYYY-MM-DD.xls)</param>

        public void doExport(List<DataSet> list, string strExcelFileName)

        {

            //object oMissing = System.Reflection.Missing.Value;

            //Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Excel.ApplicationClass();

            ////xlApp.Application.Workbooks.Add(true);	

            //try

            //{

            //    Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            //    Excel.Worksheet xlSheet;

            //    xlSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(oMissing, oMissing, 1, oMissing);

            //    xlSheet.Name = ds.DataSetName;

            //    int ExcelRowNumber = 1;  //记录数据集里表所在的行

            //    foreach (DataSet ds in list)

            //    {

            //        for (int i = 0; i < ds.Tables.Count; i++)

            //        {

            //            string strBuffer = "";

            //            int tableRowAcount = 0;  //记录单个表的行数

            //            for (int j = 0; j < ds.Tables[i].Rows.Count; j++)

            //            {

            //                for (int k = 0; k < ds.Tables[i].Columns.Count; k++)

            //                {

            //                    strBuffer += ds.Tables[i].Rows[j][k].ToString();

            //                    if (k < ds.Tables[i].Columns.Count - 1)

            //                        strBuffer += "\t";

            //                }

            //                strBuffer += "\n";

            //                tableRowAcount++;

            //            }

            //            System.Windows.Forms.Clipboard.SetDataObject("");

            //            System.Windows.Forms.Clipboard.SetDataObject(strBuffer);

            //            ((Excel.Range)xlSheet.Cells[ExcelRowNumber, 1]).Select();

            //            xlSheet.Paste(oMissing, oMissing);

            //            System.Windows.Forms.Clipboard.SetDataObject("");

            //            ExcelRowNumber += tableRowAcount;

            //            ExcelRowNumber++;

            //        }	 //end for

            //    }

            //    xlWorkBook.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);

            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);

            //    xlWorkBook = null;

            //}

            //catch (Exception ex)

            //{

            //    MessageBox.Show(ex.Message);

            //}

            //finally

            //{

            //    xlApp.Quit();

            //    xlApp = null;

            //    GC.Collect();

            //}

        }

------解决思路----------------------
用这个导出,而不是html方式导出,html根本没有工作簿的概念
------解决思路----------------------
1楼代码引用的Microsoft.Office.Interop.Excel,你服务器上必须按照office,而且必须将组件注册,还要将程序集发布为32位,然后IIS里设置允许使用32位程序集,怪麻烦的.
还是用NPOI方便.
------解决思路----------------------
的确啊..

使用组件npoi多个sheet无非就是


hssfworkbook.CreateSheet("业务部");

hssfworkbook.CreateSheet("研发部");

hssfworkbook.CreateSheet("财务部");


这就创建了3个sheet,在代码里 叫



ISheet sheet 


至于每个部门的sheet里 写入这个部门所有的用户无非就是循环结果集给单元格赋值而已.



            //表头

       IRow row = sheet.CreateRow(0);

            for (int i = 0; i < dt.Columns.Count; i++)

            {

                ICell cell = row.CreateCell(i);

                cell.SetCellValue(dt.Columns[i].ColumnName);

            }


            //数据

      for (int i = 0; i < dt.Rows.Count; i++)

            {

                IRow row1 = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    ICell cell = row1.CreateCell(j);

                    cell.SetCellValue(dt.Rows[i][j].ToString());

                }

            }


当然其中



sheet.CreateRow(0);

是你第一个ISheet变量而已..

循环部门的结果集就行...具体你自己下载那个Demo吧
------解决思路----------------------


Object Nothing = System.Reflection.Missing.Value;

            object filename = filePath; //文件保存路径


            //实例化Excel对象

            Excel.Application myexcel = new Excel.ApplicationClass();



            //添加工作表

            Workbook myworkbook = myexcel.Application.Workbooks.Add(true);

            Excel.Worksheet myworksheet = (Excel.Worksheet)myexcel.Worksheets["Sheet1"];

            myworksheet.Name = "HobbyGaGa";

            //定义一个区域范围

            Excel.Range myrange = myexcel.get_Range("A1", "AM1");


            //显示excel程序

            myexcel.Visible = false;


            int hw_i = 0, hg_i = 0, rc_i = 0, sh_i = 0, hr_i = 0, hy_i = 0, rw_i = 0;

            int hw_count = 0, hg_count = 0, rc_count = 0, sh_count = 0, hr_count = 0, hy_count = 0, rw_count = 0;


            //单元格行高

            myworksheet.Rows.RowHeight = 25;

            //单元格字体大小

            myworksheet.Rows.Font.Size = 12;

            //单元格字体

            myworksheet.Rows.Font.Name = "宋体";

            myworksheet.Rows.VerticalAlignment = XlHAlign.xlHAlignCenter;

            myworksheet.Rows.WrapText = true;

          //第一行（列名）

            myworksheet.Cells[1, 1] = "订单来源";

            myworksheet.get_Range(myworksheet.Cells[1, 1], myworksheet.Cells[1, 1]).ColumnWidth = 12;

            myworksheet.Cells[1, 2] = "日期";

            myworksheet.get_Range(myworksheet.Cells[1, 2], myworksheet.Cells[1, 2]).ColumnWidth = 20;

            myworksheet.Cells[1, 3] = "订单号";

            myworksheet.get_Range(myworksheet.Cells[1, 3], myworksheet.Cells[1, 3]).ColumnWidth = 10;


          //调整格式

 myworksheet.get_Range(myworksheet.Cells[1, 1], myworksheet.Cells[1, 42]).RowHeight = 42.75;

            myworksheet.get_Range(myworksheet.Cells[1, 1], myworksheet.Cells[1, 42]).Font.Bold = true;

            myworksheet.get_Range(myworksheet.Cells[1, 1], myworksheet.Cells[1, 42]).Rows.VerticalAlignment = XlHAlign.xlHAlignCenter;

            myworksheet.get_Range(myworksheet.Cells[1, 1], myworksheet.Cells[1, 42]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

            myworksheet.get_Range(myworksheet.Cells[1, 6], myworksheet.Cells[1, 30]).Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);

            myworksheet.get_Range(myworksheet.Cells[1, 31], myworksheet.Cells[1, 42]).Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153);

           //复制工作区 并重命名

          myworksheet.Copy(Type.Missing, myworkbook.Sheets[1]);

            myworksheet.Copy(Type.Missing, myworkbook.Sheets[1]);

            myworksheet.Copy(Type.Missing, myworkbook.Sheets[1]);

            myworksheet.Copy(Type.Missing, myworkbook.Sheets[1]);

            myworksheet.Copy(Type.Missing, myworkbook.Sheets[1]);

            myworksheet.Copy(Type.Missing, myworkbook.Sheets[1]);

            Excel.Worksheet sheet_hw = (Excel.Worksheet)myworkbook.Worksheets[2];

            Excel.Worksheet sheet_rc = (Excel.Worksheet)myworkbook.Worksheets[3];

            Excel.Worksheet sheet_sh = (Excel.Worksheet)myworkbook.Worksheets[4];

            Excel.Worksheet sheet_hr = (Excel.Worksheet)myworkbook.Worksheets[5];

            Excel.Worksheet sheet_hy = (Excel.Worksheet)myworkbook.Worksheets[6];

            Excel.Worksheet sheet_rw = (Excel.Worksheet)myworkbook.Worksheets[7];

            sheet_hw.Name = "HobbyWOW";

            sheet_rc.Name = "RCMaster";

            sheet_sh.Name = "SureHobby";

            sheet_hr.Name = "HobbyReal";

            sheet_hy.Name = "HobbyYES";

            sheet_rw.Name = "RCWOW";

这样就有了多个工作区

 foreach (var order in orders) //改成你的遍历table  放到你的工作区了

if(部门1)

  myworksheet.Cells[hg_i + hg_count + 2, 1] = order.FromPT;

                    myworksheet.Cells[hg_i + hg_count + 2, 2] = order.CreatedOn.ToString("yyyy-MM-dd HH:mm");

                    myworksheet.Cells[hg_i + hg_count + 2, 3] = order.OrderId.ToString();

相关推荐