欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

excel生成多个sheet .net

程序员文章站 2022-04-19 17:21:47
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Ap...

Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application(); //生成一个excel对象
            Microsoft.Office.Interop.Excel.Workbook workbook = myExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //生成一个workbook对象

            Microsoft.Office.Interop.Excel.Worksheet Sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得第一个sheet

            Object Nothing = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Nothing, Sheet, Nothing, Nothing);//添加一个sheet 即第二个sheet   其中第一个参数表示 添加的位置在哪个sheet之前;第二个说在之后;第三个是要新建的工作表的数目,默认值为 1;第四个指定工作表类型,类型值可为:xlWorksheet、xlChart、xlExcel4MacroSheet 或 xlExcel4IntlMacroSheet。

 

//第一行的样式

 Microsoft.Office.Interop.Excel.Range range1 = Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[1, column.Length]);//第一行
            //range1.Interior.ColorIndex = 15;//15代表灰色  
            range1.Font.Bold = true; //字体加粗
            range1.Font.Size = 14;//字体大小
            range1.RowHeight = 20;//行高
            range1.MergeCells = true;//合并单元格
            range1.VerticalAlignment = XlVAlign.xlVAlignCenter;//上下居中
            range1.HorizontalAlignment = XlHAlign.xlHAlignCenter;//左右居中

 

 ////为第一个sheet报表填充数据
            if (dt != null)
            {
                tot = 2 * (dt.Rows.Count + 2) - 2; //画excel数据开始位置
                if (dt.Rows.Count > 0)
                {
                    int n = 0;

                    for (iRow = 3; iRow <= tot; iRow++)
                    {
                        int rw = iRow;
                        iCol = 1;
                        Sheet.Cells[rw, iCol] = dt.Rows[n][0].ToString();  //单元格里面加数据
                        Sheet.Cells[rw, iCol + 1] = dt.Rows[n][1].ToString();
                        Sheet.Cells[rw, iCol + 2] = dt.Rows[n][3].ToString();
                        rw = rw + 1;
                        Sheet.Cells[rw, iCol] = "";
                        Sheet.Cells[rw, iCol + 1] = dt.Rows[n][2].ToString();
                        Sheet.Cells[rw, iCol + 2] = dt.Rows[n][4].ToString();
                        totSNum += Convert.ToInt32(dt.Rows[n][3].ToString());
                        totBNum += Convert.ToInt32(dt.Rows[n][4].ToString());
                        iRow++;
                        n++;
                    }
                    Sheet.Cells[tot + 1, 1] = "Total:送检数量";
                    Sheet.Cells[tot + 2, 1] = "Total:不合格品数量";
                    Sheet.Cells[tot + 1, 3] = totSNum.ToString();
                    Sheet.Cells[tot + 2, 3] = totBNum.ToString();
                }

}

 

//数据所在范围内加格线加粗 borders

 Microsoft.Office.Interop.Excel.Range range4 = Sheet.get_Range(Sheet.Cells[1, 3], Sheet.Cells[tot + 2, column.Length]);
            range4.Borders.LineStyle = XlLineStyle.xlContinuous;

 

 

//第二个sheet的样式

 Microsoft.Office.Interop.Excel.Range rangeT5 = Sheet1.get_Range(Sheet1.Cells[1, 1], Sheet1.Cells[1, longColum]);//第一行
                rangeT5.Interior.ColorIndex = 37;//15代表灰色  
                // rangeT5.Font.Bold = true;
                rangeT5.Font.Size = 11;
                rangeT5.RowHeight = 20;
                rangeT5.VerticalAlignment = XlVAlign.xlVAlignCenter;
                rangeT5.HorizontalAlignment = XlHAlign.xlHAlignCenter;

//为二个sheet填充数据

同sheet1添加数据

 

//数据填充结束,要关闭excel对象

 

 string strFileNm = DateTime.Now.ToString("yyyyMMddHHmmssffff")+ ".XLS";
            string strFileName = strAbsolutePath + strFileNm ;
                    object ob = System.Reflection.Missing.Value;
                     myExcel.ActiveWorkbook.SaveAs(strFileName, Nothing, Nothing, Nothing, Nothing, Nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
            //关闭Excel Work Book对象
            myExcel.ActiveWorkbook.Close(Nothing, Nothing, Nothing);

            //关闭Excel组件对象 www.2cto.com
            myExcel.Quit();

 

 return strRelativePath+strFileNm; //相对路径

response.redirect(strRelativePath+strFileNm);//可以查看生成的excel