/// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="sheetName">导出的Sheet 名称</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public MemoryStream DataTableToExcelSession(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;

            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();
            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return null;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                //合并行行列索引
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 10));
                //sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 10));

                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 1, 1));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 2, 2));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 3, 3));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 4, 4));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 5, 5));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 6, 6));
                sheet.AddMergedRegion(new CellRangeAddress(3, 4, 7, 7));
               

                if (workbook != null)
                {
                    System.IO.MemoryStream ms = new System.IO.MemoryStream();
                    workbook.Write(ms);
                    ms.Seek(0, SeekOrigin.Begin);
                    return ms;
                }
                return null;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

 

posted on 2022-01-21 18:28  高兴happy  阅读(294)  评论(0编辑  收藏  举报