首页 > 其他分享 >ExcelHelper

ExcelHelper

时间:2024-08-02 15:28:39浏览次数:16  
标签:sheet cell workbook new dt ExcelHelper row

public class ExcelHelper
    {

        private readonly IHostingEnvironment _hostingEnvironment;

        public ExcelHelper(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }

        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt">导出存放文件夹路径</param>
        /// <param name="Route">文件夹路径</param>
        /// <param name="file">导出路径(包括文件名与扩展名),注:年月日时分秒+文件名</param>
        public static void TableToExcel(DataTable dt, string Route, string file)
        {
            try
            {
                //string[] fileArr = file.Split(new char[] { '.' });
                //file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];

                IWorkbook workbook;
                string fileExt = Path.GetExtension(file).ToLower();
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
                if (workbook == null) { return; }
                ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

                //设置列宽
                //sheet.SetColumnWidth(0, 15 * 256);
                //sheet.SetColumnWidth(1, 15 * 256);
                //sheet.SetColumnWidth(2, 15 * 256);
                //sheet.SetColumnWidth(3, 15 * 256);
                //sheet.SetColumnWidth(4, 15 * 256);
                //sheet.SetColumnWidth(5, 25 * 256);
                //sheet.SetColumnWidth(6, 25 * 256);
                //sheet.SetColumnWidth(7, 30 * 256);
                //sheet.SetColumnWidth(8, 15 * 256);
                //sheet.SetColumnWidth(9, 15 * 256);
                //sheet.SetColumnWidth(10, 15 * 256);
                //sheet.SetColumnWidth(12, 15 * 256);

                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    sheet.SetColumnWidth(i, 20 * 256);
                //}


                //表头  
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count - 1; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                ICellStyle style = workbook.CreateCellStyle();
                ICellStyle style2 = workbook.CreateCellStyle();
                string val = "";
                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count - 1; j++)
                    {
                        ICell cell = row1.CreateCell(j);

                        val = dt.Rows[i][j].ToString();

                        cell.SetCellValue(val);

                    }
                }

                //合并单元格
                CellRangeAddress rowCellRangeAddress = new CellRangeAddress(1, 5, 0, 8);
                sheet.AddMergedRegion(rowCellRangeAddress);

                //设置列宽
                sheet.SetColumnWidth(4, 20 * 256);
                //设置行高
                HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IRow row2 = sheet.GetRow(2);
                row2.HeightInPoints = 120;
                row2.GetCell(2).CellStyle = cellStyle;
                setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, 2, 4);


                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream, true);
                var buf = stream.ToArray();

                //如找不到文件夹则创建   
                if (!Directory.Exists(Route))
                {
                    Directory.CreateDirectory(Route);
                }


                //保存为Excel文件  
                using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }


                //下载部分 测试无效

                //HttpResponse response = HttpContext.Current.Response;
                //response.Clear();
                //response.ClearHeaders();
                //response.ClearContent();
                //response.Buffer = true;
                //response.AddHeader("content-disposition", string.Format("attachment; FileName={0}", file));
                //response.Charset = "GB2312";
                //response.ContentEncoding = Encoding.GetEncoding("GB2312");
                //response.ContentType = MimeMapping.GetMimeMapping(file);
                //response.WriteFile(Route + file);
                //response.Flush();
                //response.Close();

                //HttpResponseBase response = httpContext.Response;
                //response.Clear();
                //response.Buffer = false;
                //response.AddHeader("Accept-Ranges", "bytes");
                //response.AddHeader("Content-Type", GetContentTypeByFileName(fileName));
                //response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(fileName)));
                //response.AddHeader("Content-Length", buffer.Length.ToString());
                //response.OutputStream.Write(buffer, 0, buffer.Length);
                //response.Flush();
                //response.End();
            }
            catch (Exception ex)
            {

                //LoggerHelper._.Error(ex.ToString());
            }

        }


        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt">导出存放文件夹路径</param>
        /// <param name="Route">文件夹路径</param>
        /// <param name="file">导出路径(包括文件名与扩展名),注:年月日时分秒+文件名</param>
        public static void DailyTableToExcel(DataTable dt, DataTable dtWorkToday, DataTable dtTomorrowPlan, string Route, string file)
        {
            try
            {
                //string[] fileArr = file.Split(new char[] { '.' });
                //file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];

                IWorkbook workbook;
                string fileExt = Path.GetExtension(file).ToLower();
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
                if (workbook == null) { return; }
                ISheet sheet;

                DataRow[] dataRow;

                //新建样式
                ICellStyle style = workbook.CreateCellStyle();
                //字体颜色
                //创建字体
                HSSFFont ffont = (HSSFFont)workbook.CreateFont();
                //给字体加粗
                ffont.IsBold = true;
                style.Alignment = HorizontalAlignment.Center;
                //给样式添加字体
                style.SetFont(ffont);


                ICellStyle styleContent = workbook.CreateCellStyle();
                styleContent.VerticalAlignment = VerticalAlignment.Center;
                styleContent.WrapText = true;

                //ICellStyle styleBorder = workbook.CreateCellStyle();
                //styleBorder.BorderBottom = BorderStyle.Medium;
                //styleBorder.BorderLeft = BorderStyle.Medium;
                //styleBorder.BorderRight = BorderStyle.Medium;
                //styleBorder.BorderTop = BorderStyle.Medium;

                //styleBorder.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                //styleBorder.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                //styleBorder.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                //styleBorder.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

                object WechatId, DailyID, UserName, Position, ImgPath, CreateDate;
                object WorkItem, Problem, ResolvedState, PlanDescription;
                string[] ImgPathArr = new string[0];
                string SheetName = "", ImgPath2 = "";
                int RowsIndex = 0;
                int SheetIndex = 2;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    RowsIndex = 0;
                    WechatId = dt.Rows[i]["WechatId"];
                    DailyID = dt.Rows[i]["DailyID"];
                    UserName = dt.Rows[i]["UserName"];
                    Position = dt.Rows[i]["Position"];
                    ImgPath = dt.Rows[i]["ImgPath"];
                    if (ImgPath != null)
                    {
                        if (ImgPath.ToString() != "")
                        {
                            ImgPathArr = ImgPath.ToString().Split(new char[] { '|' });
                        }

                    }
                    CreateDate = dt.Rows[i]["CreateDate"];
                    CreateDate = DateTime.Parse(CreateDate.ToString()).ToString("yyyyMMdd");
                    if (workbook.GetSheet(CreateDate.ToString()) != null)
                    {
                        SheetName = CreateDate.ToString() + "-" + SheetIndex;
                        SheetIndex++;
                    }
                    else
                    {
                        SheetName = CreateDate.ToString();
                        SheetIndex = 2;
                    }

                    sheet = workbook.CreateSheet(SheetName);

                    IRow row = sheet.CreateRow(RowsIndex);
                    RowsIndex++;
                    ICell cell = row.CreateCell(0);
                    cell.SetCellValue("" + UserName + "(" + Position + ")  " + CreateDate + "日报");
                    cell.CellStyle = style;//设置样式
                    sheet.SetColumnWidth(0, 50 * 256);

                    ////合并单元格
                    CellRangeAddress rowCellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
                    sheet.AddMergedRegion(rowCellRangeAddress);

                    row = sheet.CreateRow(RowsIndex);
                    RowsIndex++;
                    cell = row.CreateCell(0);
                    cell.SetCellValue("今日工作");
                    cell.CellStyle = style;//设置样式
                    cell = row.CreateCell(1);
                    cell.SetCellValue("问题点");
                    cell.CellStyle = style;//设置样式
                    cell = row.CreateCell(2);
                    cell.SetCellValue("解决状态");
                    cell.CellStyle = style;//设置样式

                    sheet.SetColumnWidth(0, 50 * 256);
                    sheet.SetColumnWidth(1, 50 * 256);


                    dataRow = dtWorkToday.Select("DailyID='" + DailyID + "'");
                    //今日工作
                    for (int j = 0; j < dataRow.Length; j++)
                    {
                        WorkItem = dataRow[j]["WorkItem"];
                        Problem = dataRow[j]["Problem"];
                        ResolvedState = dataRow[j]["ResolvedState"];
                        //数据 循环填充内容部分
                        row = sheet.CreateRow(RowsIndex);
                        row.HeightInPoints = 60;
                        RowsIndex++;
                        cell = row.CreateCell(0);
                        cell.CellStyle = styleContent;
                        cell.SetCellValue(WorkItem.ToString());
                        cell = row.CreateCell(1);
                        cell.CellStyle = styleContent;
                        cell.SetCellValue(Problem.ToString());
                        cell = row.CreateCell(2);
                        cell.CellStyle = styleContent;
                        cell.SetCellValue(ResolvedState.ToString() + "%");
                        row = sheet.CreateRow(RowsIndex);//空行

                        //合并单元格
                        rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
                        RowsIndex++;
                        sheet.AddMergedRegion(rowCellRangeAddress);
                    }


                    row = sheet.CreateRow(RowsIndex);
                    cell = row.CreateCell(0);
                    cell.SetCellValue("明日计划");
                    cell.CellStyle = style;//设置样式
                    sheet.SetColumnWidth(0, 50 * 256);

                    //合并单元格
                    rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
                    RowsIndex++;
                    sheet.AddMergedRegion(rowCellRangeAddress);


                    dataRow = dtTomorrowPlan.Select("DailyID='" + DailyID + "'");
                    //明日计划
                    for (int k = 0; k < dataRow.Length; k++)
                    {
                        PlanDescription = dataRow[k]["PlanDescription"];
                        //数据 循环填充内容部分
                        row = sheet.CreateRow(RowsIndex);
                        row.HeightInPoints = 60;
                        cell = row.CreateCell(0);
                        cell.CellStyle = styleContent;
                        cell.SetCellValue(PlanDescription.ToString());
                        //合并单元格
                        rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
                        RowsIndex++;
                        sheet.AddMergedRegion(rowCellRangeAddress);


                        row = sheet.CreateRow(RowsIndex);//空行

                        //合并单元格
                        rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
                        RowsIndex++;
                        sheet.AddMergedRegion(rowCellRangeAddress);


                    }


                    row = sheet.CreateRow(RowsIndex);
                    cell = row.CreateCell(0);
                    cell.SetCellValue("图片列表");
                    cell.CellStyle = style;//设置样式
                    sheet.SetColumnWidth(0, 50 * 256);

                    //合并单元格
                    rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
                    RowsIndex++;
                    sheet.AddMergedRegion(rowCellRangeAddress);

                    int rowline = RowsIndex, col = 0;
                    //图片列表
                    for (int z = 0; z < ImgPathArr.Length; z++)
                    {
                        col = z;
                        if (col > 1 && col % 2 == 0)
                        {
                            col = 0;
                            rowline += 10;
                        }

                        if (col > 1 && col % 2 == 1)
                        {
                            col = 1;

                        }

                        ImgPath2 = ImgPathArr[z];
                        ImgPath2 = ImgPath2.Substring(ImgPath2.IndexOf("FileManage") - 1);

                        //插入图片
                        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                        setPic((HSSFWorkbook)workbook, patriarch, ImgPath2, sheet, rowline, col);
                        //setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, rowline, col);

                    }

                }




                //新建工作表
                //ISheet Sheet1 = workbook.CreateSheet("Sheet1");
                //CreateSheet(workbook, "20201106");

                //表头  
                //IRow row = sheet.CreateRow(0);
                //for (int i = 0; i < dt.Columns.Count - 1; i++)
                //{
                //    ICell cell = row.CreateCell(i);
                //    cell.SetCellValue(dt.Columns[i].ColumnName);
                //}


                //ICellStyle style = workbook.CreateCellStyle();
                //ICellStyle style2 = workbook.CreateCellStyle();
                //string val = "";
                ////数据  
                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    IRow row1 = sheet.CreateRow(i + 1);
                //    for (int j = 0; j < dt.Columns.Count - 1; j++)
                //    {
                //        ICell cell = row1.CreateCell(j);

                //        val = dt.Rows[i][j].ToString();

                //        cell.SetCellValue(val);

                //    }
                //}

                ////合并单元格
                //CellRangeAddress rowCellRangeAddress = new CellRangeAddress(1, 5, 0, 8);
                //sheet.AddMergedRegion(rowCellRangeAddress);

                ////设置列宽
                //sheet.SetColumnWidth(4, 20 * 256);
                ////设置行高
                //HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                //IRow row2 = sheet.GetRow(2);
                //row2.HeightInPoints = 120;
                //row2.GetCell(2).CellStyle = cellStyle;
                ////插入图片
                //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                //setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, 2, 4);


                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream, true);
                var buf = stream.ToArray();

                //如找不到文件夹则创建   
                if (!Directory.Exists(Route))
                {
                    Directory.CreateDirectory(Route);
                }


                //保存为Excel文件  
                using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }


            }
            catch (Exception ex)
            {

                //LoggerHelper._.Error(ex.ToString());
            }

        }

        /// <summary>
        /// 新建Sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="SheetName"></param>
        public static void CreateSheet(IWorkbook workbook, string SheetName)
        {

            ISheet sheet = workbook.CreateSheet(SheetName);

            //新建样式
            ICellStyle style = workbook.CreateCellStyle();
            //字体颜色
            //创建字体
            HSSFFont ffont = (HSSFFont)workbook.CreateFont();
            //给字体加粗
            ffont.IsBold = true;
            style.Alignment = HorizontalAlignment.Center;
            //给样式添加字体
            style.SetFont(ffont);


            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue("李儒鸿(.net工程师)");
            cell.CellStyle = style;//设置样式
            sheet.SetColumnWidth(0, 50 * 256);

            ////合并单元格
            CellRangeAddress rowCellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);

            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.SetCellValue("今日工作");
            cell.CellStyle = style;//设置样式
            cell = row.CreateCell(1);
            cell.SetCellValue("问题点");
            cell.CellStyle = style;//设置样式
            cell = row.CreateCell(2);
            cell.SetCellValue("解决状态");
            cell.CellStyle = style;//设置样式

            sheet.SetColumnWidth(0, 50 * 256);
            sheet.SetColumnWidth(1, 50 * 256);

            //数据 循环填充内容部分
            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.SetCellValue("今日工作内容001");
            cell = row.CreateCell(1);
            cell.SetCellValue("问题点001");
            cell = row.CreateCell(2);
            cell.SetCellValue("100%");

            row = sheet.CreateRow(3);//空行
                                     //合并单元格
            rowCellRangeAddress = new CellRangeAddress(3, 3, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);

            row = sheet.CreateRow(4);
            cell = row.CreateCell(0);
            cell.SetCellValue("明日计划");
            cell.CellStyle = style;//设置样式
            sheet.SetColumnWidth(0, 50 * 256);

            //合并单元格
            rowCellRangeAddress = new CellRangeAddress(4, 4, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);

            //数据 循环填充内容部分
            row = sheet.CreateRow(5);
            cell = row.CreateCell(0);
            cell.SetCellValue("明日计划内容001");
            //合并单元格
            rowCellRangeAddress = new CellRangeAddress(5, 5, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);


            row = sheet.CreateRow(6);//空行
                                     //合并单元格
            rowCellRangeAddress = new CellRangeAddress(6, 6, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);

            row = sheet.CreateRow(7);
            cell = row.CreateCell(0);
            cell.SetCellValue("图片列表");
            cell.CellStyle = style;//设置样式
            sheet.SetColumnWidth(0, 50 * 256);

            //合并单元格
            rowCellRangeAddress = new CellRangeAddress(7, 7, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);

            //数据 循环填充内容部分
            row = sheet.CreateRow(8);
            cell = row.CreateCell(0);
            //合并单元格
            rowCellRangeAddress = new CellRangeAddress(8, 8, 0, 2);
            sheet.AddMergedRegion(rowCellRangeAddress);

        }

        /// <summary>
        /// Datable导出成Excel 设置样式示例
        /// </summary>
        /// <param name="dt">导出存放文件夹路径</param>
        /// <param name="Route">文件夹路径</param>
        /// <param name="file">导出路径(包括文件名与扩展名),注:年月日时分秒+文件名</param>

        public static void TableToExcelStyle(DataTable dt, string Route, string file)
        {
            //string[] fileArr = file.Split(new char[] { '.' });
            //file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];

            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return; }
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

            //设置列宽
            //sheet.SetColumnWidth(0, 15 * 256);
            //sheet.SetColumnWidth(1, 15 * 256);
            //sheet.SetColumnWidth(2, 15 * 256);
            //sheet.SetColumnWidth(3, 15 * 256);
            //sheet.SetColumnWidth(4, 15 * 256);
            //sheet.SetColumnWidth(5, 25 * 256);
            //sheet.SetColumnWidth(6, 25 * 256);
            //sheet.SetColumnWidth(7, 30 * 256);
            //sheet.SetColumnWidth(8, 15 * 256);
            //sheet.SetColumnWidth(9, 15 * 256);
            //sheet.SetColumnWidth(10, 15 * 256);
            //sheet.SetColumnWidth(12, 15 * 256);

            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    sheet.SetColumnWidth(i, 20 * 256);
            //}


            //表头  
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count - 1; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }


            ICellStyle style = workbook.CreateCellStyle();
            ICellStyle style2 = workbook.CreateCellStyle();
            string val = "";
            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count - 1; j++)
                {
                    ICell cell = row1.CreateCell(j);

                    val = dt.Rows[i][j].ToString();

                    cell.SetCellValue(val);

                    if (val == "通过")
                    {
                        //设置单元格颜色
                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
                        style.FillPattern = FillPattern.SolidForeground;

                        //字体颜色
                        //创建字体
                        HSSFFont ffont = (HSSFFont)workbook.CreateFont();
                        //给字体设置颜色
                        ffont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
                        //给样式添加字体
                        style.SetFont(ffont);

                        cell.CellStyle = style;//设置
                    }

                    if (val == "测试1报废")
                    {
                        //设置单元格颜色
                        style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Orange.Index;
                        style2.FillPattern = FillPattern.SolidForeground;

                        //字体颜色
                        //创建字体
                        HSSFFont ffont = (HSSFFont)workbook.CreateFont();
                        //给字体设置颜色
                        ffont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
                        //给样式添加字体
                        style2.SetFont(ffont);

                        cell.CellStyle = style2;//设置
                    }

                }
            }



            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream, true);
            var buf = stream.ToArray();

            //如找不到文件夹则创建   
            if (!Directory.Exists(Route))
            {
                Directory.CreateDirectory(Route);
            }


            //保存为Excel文件  
            using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
                fs.Close();
                fs.Dispose();
            }


        }

        /// <summary>
        /// 复制文件
        /// </summary>
        /// <param name="sourceFilePath"></param>
        /// <param name="newFilePath"></param>
        public static void CopyTextToNewWorkbook(string sourceFilePath, string newFilePath)
        {
            /*
             * 可读取excel的公式数据和引用外部数据
            DataTable dt = new DataTable();
            Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(sourceFilePath);
            Worksheet ws = wk.Worksheets[0];
            int a = ws.Cells.Rows.Count;
            int b = ws.Cells.Columns.Count;
            dt = ws.Cells.ExportDataTable(0, 0, 9, 9);
            dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1, true);
            dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1);
            */


            // 加载现有的工作簿
            using (FileStream fileStream = new FileStream(sourceFilePath, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(fileStream);


                // 创建新的工作簿
                XSSFWorkbook newWorkbook = new XSSFWorkbook();

                // 复制每个工作表
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    ISheet newSheet = newWorkbook.CreateSheet(sheet.SheetName);

                    // 复制行
                    for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow row = sheet.GetRow(rowNum);
                        IRow newRow = newSheet.CreateRow(rowNum);

                        // 复制单元格
                        if (row != null)
                        {
                            for (int cellNum = 0; cellNum < row.LastCellNum; cellNum++)
                            {
                                ICell cell = row.GetCell(cellNum);
                                ICell newCell = newRow.CreateCell(cellNum);

                                if (cell != null)
                                {
                                    //newCell.CellStyle = cell.CellStyle;
                                    //newCell.SetCellType(cell.CellType);

                                    // 根据单元格类型复制数据
                                    switch (cell.CellType)
                                    {
                                        case CellType.String:
                                            newCell.SetCellValue(cell.StringCellValue);
                                            break;
                                        case CellType.Numeric:
                                            newCell.SetCellValue(cell.NumericCellValue);
                                            break;
                                        // ...其他类型
                                        default:
                                            break;
                                    }
                                }
                            }
                        }
                    }
                }

                // 保存新工作簿到文件
                using (FileStream newFileStream = new FileStream(newFilePath, FileMode.Create, FileAccess.Write))
                {
                    newWorkbook.Write(newFileStream);
                }
            }
        }


        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file)
        {


            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheetAt(0);

                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);// GetValueType(header.GetCell(i));

                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                    {
                        obj = obj.ToString().Trim();
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                        columns.Add(i);
                    }

                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = sheet.GetRow(i).GetCell(j);// GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);// GetValueType(sheet.GetRow(i).GetCell(j));
                        dr[j] = dr[j].ToString().Trim();
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// Excel导入成Datable
        /// 此方法可读取excel的公式数据和引用外部数据
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        public static DataTable ExcelToTable2(string file)
        {

            DataTable dt = new DataTable();
            Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
            Worksheet ws = wk.Worksheets[0];
            
            dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1, true);
            
            return dt;
        }

        public static void TemplateExcle()
        {
            FileStream file = new FileStream(@"template/book1.xls", FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
            sheet1.GetRow(1).GetCell(1).SetCellValue(200200);
            sheet1.GetRow(2).GetCell(1).SetCellValue(300);
            sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
            sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
            sheet1.GetRow(5).GetCell(1).SetCellValue(110);
            sheet1.GetRow(6).GetCell(1).SetCellValue(100);
            sheet1.GetRow(7).GetCell(1).SetCellValue(200);
            sheet1.GetRow(8).GetCell(1).SetCellValue(210);
            sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
            sheet1.GetRow(10).GetCell(1).SetCellValue(240);
            sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
            sheet1.GetRow(12).GetCell(1).SetCellValue(150);

            //Force excel to recalculate all the formula while open

            sheet1.ForceFormulaRecalculation = true;

            file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();

        }

        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.DateCellValue;

                //case CellType.Numeric: //NUMERIC:
                //    return cell.NumericCellValue;

                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }


        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="workbook">工作表格</param>
        /// <param name="patriarch">图片路径</param>
        /// <param name="path"></param>
        /// <param name="sheet">表格</param>
        /// <param name="rowline">第几行</param>
        /// <param name="col">第几列</param>
        private static void setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, int rowline, int col)
        {
            //string contentRootPath = _hostingEnvironment.ContentRootPath;
            path = "";// System.Web.HttpContext.Current.Server.MapPath(path);
            if (string.IsNullOrEmpty(path))
            {

                return;
            }

            if (!System.IO.File.Exists(path))
            {
                return;
            }

            byte[] bytes = System.IO.File.ReadAllBytes(path);

            //MemoryStream ms = new MemoryStream(bytes);
            //Image Img = Bitmap.FromStream(ms, true);
            //double ImageOriginalWidth = Img.Width;//原始图片的长度
            //double ImageOriginalHeight = Img.Height;//原始图片的宽度

            int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
            // 插图片的位置  HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
            //关于HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)的参数
            //dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离;
            //dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离;
            //dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离;
            //dy2:终止单元格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离;
            //col1:起始单元格列序号,从0开始计算;
            //row1:起始单元格行序号,从0开始计算,如例子中col1 = 0,row1 = 0就表示起始单元格为A1;
            //col2:终止单元格列序号,从0开始计算;
            //row2:终止单元格行序号,从0开始计算,如例子中col2 = 2,row2 = 2就表示起始单元格为C3;
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, rowline, col + 1, rowline + 1);
            //把图片插到相应的位置
            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            //anchor.AnchorType = AnchorType.MoveDontResize;
            //ImageOriginalWidth = ImageOriginalWidth / 50;
            //ImageOriginalHeight = ImageOriginalHeight / 50;

            pict.Resize(1, 10);

        }


    }

 

标签:sheet,cell,workbook,new,dt,ExcelHelper,row
From: https://www.cnblogs.com/zyx321/p/18338859

相关文章