Beispiel #1
0
        /// <summary>
        /// 向excel写入数据表
        /// </summary>
        public static ExcelPackage LoadFromDataTable(this ExcelPackage doc, DataTable dt, Action <DataTableOptions> dtOptionsAction)
        {
            Harry.Check.NotNull(doc, nameof(doc));
            Harry.Check.NotNull(dt, nameof(dt));

            var dtOptions = new DataTableOptions();

            dtOptionsAction?.Invoke(dtOptions);
            dtOptions.WorkbookAction?.Invoke(doc.Workbook);

            //doc.Workbook.Worksheets.Add($"{dt.TableName}")
            //    .Cells[1, 1].LoadFromDataTable(dt, false);
            //return;

            int            rownum            = 0;
            int            currentSheetIndex = 0;
            ExcelWorksheet currentSheet      = null;

            if (dt.Rows != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i % dtOptions.PageSize == 0)
                    {
                        //写入表头
                        rownum       = 1;
                        currentSheet = createSheet(doc, dt, ref rownum, $"{dt.TableName}({(++currentSheetIndex).ToString()})", dtOptions);
                    }

                    //写入数据行
                    writeRow(currentSheet, dt.Rows[i], rownum++, dtOptions);
                }
            }
            else
            {
                //无数据,写个表头然后退出
                //写入表头
                rownum       = 1;
                currentSheet = createSheet(doc, dt, ref rownum, $"{dt.TableName}({(++currentSheetIndex).ToString()})", dtOptions);
            }

            return(doc);
        }
Beispiel #2
0
        private static void writeRow(ExcelWorksheet sheet, DataRow dr, int rownum, DataTableOptions dtOptions)
        {
            if (dtOptions.DataHeight != null)
            {
                //设置行高
                sheet.Row(rownum).Height = dtOptions.DataHeight.Value;
            }
            foreach (DataColumn column in dr.Table.Columns)
            {
                var cell  = sheet.Cells[rownum, column.Ordinal + 1];
                var value = dr[column];
                if (value != null && value != DBNull.Value)
                {
                    cell.Value = dr[column];
                }

                ValidateCellStyle(cell, column.DataType);

                //设置单元格样式
                dtOptions.DataCellAction?.Invoke(column, cell);
            }
        }
Beispiel #3
0
        //创建一张新表,并写入表头信息
        private static ExcelWorksheet createSheet(ExcelPackage doc, DataTable dt, ref int rownum, string sheetName, DataTableOptions dtOptions)
        {
            ExcelWorksheet worksheet = doc.Workbook.Worksheets.Add(doc.GetValidSheetName(sheetName));

            ////写入表名称
            //var titleCell = worksheet.Cells[rownum,  1];
            //titleCell.Value = dt.TableName;

            ////设置标题样式
            //dtOptions.TitleStyleAction?.Invoke(titleCell.Style);

            //rownum++;

            //写入字段信息
            foreach (DataColumn column in dt.Columns)
            {
                var header = worksheet.Cells[rownum, column.Ordinal + 1];
                header.Value = column.Caption;
                var excelColumn = worksheet.Column(column.Ordinal + 1);

                dtOptions.HeaderCellAction?.Invoke(column, excelColumn, header);
            }
            //var fieldNameCells = worksheet.Cells[rownum, 1, rownum, dt.Columns.Count];
            //dtOptions.HeaderStyleAction?.Invoke(fieldNameCells.Style);

            if (dtOptions.HeaderHeight != null)
            {
                //设置header行高
                worksheet.Row(rownum).Height = dtOptions.HeaderHeight.Value;
            }

            //冻结首行
            if (dtOptions.FrozenHeader)
            {
                worksheet.View.FreezePanes(rownum + 1, 1);
            }

            rownum++;

            return(worksheet);
        }