コード例 #1
0
        private string BuildBillSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtMain, DataTable[] dtSub)
        {
            string errorMessage = string.Empty;
            // 在工作簿建立空白工作表
            ISheet sheet = null;

            if (!string.IsNullOrEmpty(sheetEntity.SheetName))
            {
                sheet = workBook.CreateSheet(sheetEntity.SheetName);
            }
            else
            {
                sheet = workBook.CreateSheet();
            }
            // 看是否有跳过
            int  beginRow = 0 + helpEntity.SkipRowNum;
            int  beginCol = 0 + helpEntity.SkipColNum;
            IRow rowHead  = sheet.CreateRow(beginRow);
            // 循环添加表头
            int colIndex = beginCol;
            var tempMast = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
            var tempSub  = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
            var tempCol  = tempMast.Union(tempSub);

            foreach (var item in tempCol)
            {
                if (item.Hidden)
                {
                    continue;
                }
                ICell cell = rowHead.CreateCell(colIndex);
                cell.SetCellValue(item.ExcelName);
                cell.CellStyle = item.CellStyle;
                sheet.SetColumnWidth(colIndex, item.Width);
                rowHead.Height = helpEntity.THeight;
                IName iname = workBook.CreateName();
                iname.NameName        = item.ColumnName;
                iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
                colIndex++;
            }
            if (helpEntity.FreezeTitleRow)
            {
                sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
            }
            colIndex = beginCol;
            beginRow++;
            //循环赋值内容
            for (int i = 0; i < dtMain.Rows.Count; i++)
            {
                DataRow   drMain = dtMain.Rows[i];
                DataTable dtData = dtSub[i];
                for (int j = 0; j < dtData.Rows.Count; j++)
                {
                    DataRow drSub = dtData.Rows[j];
                    IRow    row   = sheet.CreateRow(beginRow);
                    ICell   cell  = null;
                    colIndex = beginCol;
                    foreach (var col in tempCol)
                    {
                        if (col.Hidden)
                        {
                            continue;
                        }
                        cell = row.CreateCell(colIndex);
                        object value = null;
                        if (col.PrimaryMark == true)
                        {
                            value = drMain[col.ColumnName];
                            if (helpEntity.OneMain == true && j > 0)
                            {
                                colIndex++;
                                continue;
                            }
                        }
                        value = drSub[col.ColumnName];
                        cell.SetCellValue(value, col, col.CellStyle);
                        colIndex++;
                    }
                    beginRow++;
                }
            }
            // 筛选
            if (helpEntity.AutoFilter)
            {
                CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
                sheet.SetAutoFilter(c);
            }
            sheet.DisplayGridlines = helpEntity.ShowGridLine;
            ProcessSheet(sheet, helpEntity);
            return(errorMessage);
        }
コード例 #2
0
        /// <summary>
        /// 简单列表数据导出
        /// 这里DataTable数据是已经排序过的,表头名称
        /// </summary>
        /// <returns></returns>
        private string RunExportSimple(ExportRunEntity helpEntity, params DataTable[] dtData)
        {
            /** 模板类型:标题行和内容行有边框, 其他网格线取消
             *  标题行     XX  XX  XX  XX
             *  内容行     xx  xx  xx  xx
             *
             *  自定义内容 XX:xx     XX:xx
             * */
            string errorMessage = string.Empty;

            if (dtData == null)
            {
                return("导出数据为空");
            }
            foreach (var data in dtData)
            {
                if (data == null || data.Rows.Count == 0)
                {
                    errorMessage = "导出数据为空";
                    break;
                }
            }
            if (!string.IsNullOrEmpty(errorMessage))
            {
                return(errorMessage);
            }
            if (helpEntity.ExportColumns == null || helpEntity.ExportColumns.Count() == 0)
            {
                return("导出配置为空");
            }
            if (!string.IsNullOrEmpty(errorMessage))
            {
                return(errorMessage);
            }

            // 处理工作簿、文件名
            string fileName = BeforeDealSheet(helpEntity);
            // 处理每个工作表数据
            ExportSheetEntity sheetEntity = null;

            for (int i = 0; i < dtData.Length; i++)
            {
                sheetEntity = new ExportSheetEntity();
                if (i < helpEntity.SheetName.Count)
                {
                    sheetEntity.SheetName = helpEntity.SheetName[i];
                }
                if (i < helpEntity.SheetTitle.Count)
                {
                    sheetEntity.SheetTitle = helpEntity.SheetTitle[i];
                }
                errorMessage = BuildSinpleSheet(helpEntity, sheetEntity, dtData[i]);
                if (!string.IsNullOrEmpty(errorMessage))
                {
                    break;
                }
            }
            // 如果存在错误,则终止执行
            if (!string.IsNullOrEmpty(errorMessage))
            {
                return(errorMessage);
            }

            // 写入Http
            HttpWrite(fileName);

            return(errorMessage);
        }
コード例 #3
0
        private string BuildRecSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataRow drMain, DataTable dtSub)
        {
            string errorMessage = string.Empty;
            ISheet sheet        = null;

            if (!string.IsNullOrEmpty(sheetEntity.SheetName))
            {
                sheet = workBook.CreateSheet(sheetEntity.SheetName);
            }
            else
            {
                sheet = workBook.CreateSheet();
            }
            // 设置开始行和列
            int beginRow = 0 + helpEntity.SkipRowNum;
            int beginCol = 0 + helpEntity.SkipColNum;

            #region 对表头数据进行赋值
            // 标题行
            IRow  rowTitle  = sheet.CreateRow(beginRow);
            ICell cellTitle = rowTitle.CreateCell(beginCol);
            cellTitle.SetCellValue(sheetEntity.SheetTitle);
            // 设置下划线
            IFont fontLine = workBook.CreateFont();
            fontLine.Underline = FontUnderlineType.Single;
            if (helpEntity.TitleBoldMark == true)
            {
                fontLine.FontHeight = (double)FontBoldWeight.Bold;
            }
            cellTitle.CellStyle.SetFont(fontLine);
            cellTitle.CellStyle.Alignment         = HorizontalAlignment.Center;
            cellTitle.CellStyle.VerticalAlignment = VerticalAlignment.Center;
            rowTitle.Height = helpEntity.THeight;
            // 标题行合并
            sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + helpEntity.ExportColumns.Count() - 1));
            beginRow++;
            // 表头字段集合
            var masterColumn     = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
            var subColumn        = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
            int maxColIndex      = masterColumn.Select(t => t.ColIndex).Max();
            int minColIndex      = masterColumn.Select(t => t.ColIndex).Min();
            var listTitleContent = masterColumn;
            var temp             = masterColumn.Select(t => t.RowIndex).Distinct();
            int colIndex         = beginCol;
            // 循环赋值表头数据
            foreach (var rowIndex in temp)
            {
                IRow rowContent = sheet.CreateRow(beginRow);
                var  curRow     = listTitleContent.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex);
                colIndex = beginCol;
                var curColMinIndex = curRow.Select(t => t.ColIndex).Min();
                var diff           = curColMinIndex - minColIndex;
                // 此处判断主要是为了有跳过列的
                if (diff > 0)
                {
                    // 标题
                    colIndex += diff;
                    // 内容
                    colIndex += diff;
                }
                foreach (var item in curRow)
                {
                    colIndex = colIndex - item.diffNum;
                    // 先赋值标题,再赋值
                    ICell curTitle = rowContent.CreateCell(colIndex);
                    curTitle.SetCellValue(item.ExcelName);
                    curTitle.CellStyle = item.CellStyle;
                    IName iname = workBook.CreateName();
                    iname.NameName        = item.ColumnName;
                    iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
                    colIndex++;
                    ICell  cell      = rowContent.CreateCell(colIndex);
                    string curColumn = item.ColumnName;
                    object curValue  = drMain[curColumn];
                    if (item.TitleColSpan > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + item.TitleColSpan - 1));
                        colIndex++;
                    }
                    cell.SetCellValue(curValue, item, item.CellStyle);
                    colIndex++;
                }
                beginRow++;
            }
            #endregion
            // 获取和循环设置表体表体行
            var  listTitle   = helpEntity.ExportColumns;
            IRow rowSubTitle = sheet.CreateRow(beginRow);
            colIndex = beginCol;
            foreach (var item in listTitle)
            {
                ICell cellSubTitle = rowSubTitle.CreateCell(colIndex);
                cellSubTitle.SetCellValue(item.ExcelName);
                sheet.SetColumnWidth(colIndex, item.Width);
                colIndex++;
            }
            beginRow++;
            // 冻结上述行和列
            if (helpEntity.FreezeTitleRow)
            {
                sheet.CreateFreezePane(beginCol, beginRow - 1, beginCol, beginRow - 1);
            }
            // 循环赋值列表数据
            foreach (DataRow dr in dtSub.Rows)
            {
                IRow rowSubContent = sheet.CreateRow(beginRow);
                colIndex = beginCol;
                foreach (var item in listTitle)
                {
                    ICell  cellSubContent = rowSubContent.CreateCell(colIndex);
                    object curValue       = dr[item.ColumnName];
                    // 设置表体内容
                    cellSubContent.SetCellValue(curValue, item, item.CellStyle);
                    colIndex++;
                }
                beginRow++;
            }
            // 筛选
            if (helpEntity.AutoFilter)
            {
                CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
                sheet.SetAutoFilter(c);
            }
            sheet.DisplayGridlines = helpEntity.ShowGridLine;
            ProcessSheet(sheet, helpEntity);
            return(errorMessage);
        }
コード例 #4
0
        private string BuildMergeSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
        {
            string errorMessage = string.Empty;

            // 设置开始行、列
            int    beginRow = 0 + helpEntity.SkipRowNum;
            int    beginCol = 0 + helpEntity.SkipColNum;
            ISheet sheet    = workBook.CreateSheet();

            #region 标题处理
            IRow rowTitle1 = sheet.CreateRow(beginRow);
            rowTitle1.Height = helpEntity.THeight;
            int colIndex = beginCol;
            // 先赋值合并行标题
            foreach (var mergeName in sheetEntity.MergeNames)
            {
                var temp = helpEntity.ExportColumns.Where(t => t.MergeName == mergeName);
                var len  = temp.Count();
                // 获取合并标题开始列位置
                int index = GetIndex(helpEntity.ExportColumns, mergeName);
                colIndex = index;
                ICell cellMerge = rowTitle1.CreateCell(colIndex);
                cellMerge.SetCellValue(mergeName);
                //cellMerge.CellStyle = cellStyleHead;
                // 设置合并
                sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + len - 1));
                //for (int tempIndex = colIndex; tempIndex < colIndex + len; tempIndex++)
                //    HSSFCellUtil.GetCell(rowTitle1, tempIndex).CellStyle = cellStyleHead;
            }
            beginRow++;
            IRow rowTitle2 = sheet.CreateRow(beginRow);
            rowTitle2.Height = helpEntity.THeight;
            colIndex         = beginCol;
            foreach (var item in helpEntity.ExportColumns)
            {
                ICell cellTitle = rowTitle2.CreateCell(colIndex);
                cellTitle.SetCellValue(item.ExcelName);
                cellTitle.CellStyle = item.CellStyle;
                // 如果此标题在上述没有合并,则向上合并一列,保持美观
                if (string.IsNullOrEmpty(item.MergeName))
                {
                    sheet.AddMergedRegion(new CellRangeAddress(beginRow - 1, beginRow, colIndex, colIndex));
                    ICell cellTemp = HSSFCellUtil.GetCell(rowTitle1, colIndex);
                    //cellTemp.CellStyle = cellStyleHead;
                    cellTemp.SetCellValue(item.ExcelName);
                }
                sheet.SetColumnWidth(colIndex, item.Width);
                colIndex++;
            }
            // 冻结标题行
            if (helpEntity.FreezeTitleRow)
            {
                sheet.CreateFreezePane(beginCol + helpEntity.MergeColNum, 2, beginCol + helpEntity.MergeColNum, 2);
            }
            #endregion

            beginRow++;
            // 循环赋值列表内容
            foreach (DataRow dr in dtData.Rows)
            {
                IRow rowContent = sheet.CreateRow(beginRow);
                rowContent.Height = helpEntity.CHeight;
                colIndex          = beginCol;
                ICell cell = null;
                // 赋值内容
                foreach (var item in helpEntity.ExportColumns)
                {
                    if (item.Hidden)
                    {
                        continue;
                    }
                    cell = rowContent.CreateCell(colIndex);
                    object curValue = dr[item.ColumnName];
                    cell.SetCellValue(curValue, item, item.CellStyle);
                    colIndex++;
                }
                rowContent.Height = helpEntity.CHeight;
                beginRow++;
            }
            // 筛选
            if (helpEntity.AutoFilter)
            {
                CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
                sheet.SetAutoFilter(c);
            }
            sheet.DisplayGridlines = helpEntity.ShowGridLine;
            ProcessSheet(sheet, helpEntity);
            return(errorMessage);
        }
コード例 #5
0
        /// <summary>
        /// 处理每个工作表数据
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="helpEntity"></param>
        /// <returns></returns>
        private string BuildSinpleSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
        {
            string errorMessage = string.Empty;
            // 在工作簿建立空白工作表
            ISheet sheet = null;

            if (!string.IsNullOrEmpty(sheetEntity.SheetName))
            {
                sheet = workBook.CreateSheet(sheetEntity.SheetName);
            }
            else
            {
                sheet = workBook.CreateSheet();
            }
            // 看是否有跳过
            int  beginRow = 0 + helpEntity.SkipRowNum;
            int  beginCol = 0 + helpEntity.SkipColNum;
            IRow rowHead  = sheet.CreateRow(beginRow);
            // 循环添加表头
            int colIndex = beginCol;

            foreach (var item in helpEntity.ExportColumns)
            {
                if (item.Hidden)
                {
                    continue;
                }
                ICell cell = rowHead.CreateCell(colIndex);
                cell.SetCellValue(item.ExcelName);
                cell.CellStyle = item.CellStyle;
                sheet.SetColumnWidth(colIndex, item.Width);
                rowHead.Height = helpEntity.THeight;
                IName iname = workBook.CreateName();
                iname.NameName        = item.ColumnName;
                iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
                colIndex++;
            }
            if (helpEntity.FreezeTitleRow)
            {
                sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
            }
            colIndex = beginCol;
            beginRow++;
            //循环赋值内容
            foreach (DataRow dr in dtData.Rows)
            {
                IRow  rowContent = sheet.CreateRow(beginRow);
                ICell cell       = null;
                colIndex = beginCol;
                foreach (var item in helpEntity.ExportColumns)
                {
                    if (item.Hidden)
                    {
                        continue;
                    }
                    cell = rowContent.CreateCell(colIndex);
                    object curValue = dr[item.ColumnName];
                    cell.SetCellValue(curValue, item, item.CellStyle);
                    colIndex++;
                }
                rowContent.Height = (short)helpEntity.CHeight;
                beginRow++;
            }
            // 筛选
            if (helpEntity.AutoFilter)
            {
                CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
                sheet.SetAutoFilter(c);
            }
            sheet.DisplayGridlines = helpEntity.ShowGridLine;
            ProcessSheet(sheet, helpEntity);
            return(errorMessage);
        }
コード例 #6
0
        /// <summary>
        /// 对账单类型导出
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="dtMain"></param>
        /// <param name="dtSub"></param>
        /// <param name="helpEntity"></param>
        /// <returns></returns>
        private string RunExportReconciliation(ExportRunEntity helpEntity, DataTable dtMain, params DataTable[] dtSub)
        {
            /** 模板类型:标题行以上(含)需要冻结,使excel拉动时一直显示
             *                         xxx 对账单   (加上下划线)
             *  供方:xxx      电话:xxx       传值:xxx     货币类别:xxx(需要加批准)
             *  客户:xxx      电话:xxx       传值:xxx     核对日期:xxx
             *                                 扣款金额:xxx 金额合计:xxx
             *  标题行     XXX     XXX     XXX     XXX     XXX     XXX     XXX     XXX
             *  内容行     xxx     xxx     xxx     xxx     xxx     xxx     xxx     xxx
             *
             *  批准:如下
             *  提示:
             *  RMB:人民币,单价为含税价格
             *  USD:美元,单价为不含税价格
             * */
            #region 前期判判断和导出准备
            string errorMessage = string.Empty;
            if (dtMain == null || dtMain.Rows.Count == 0 || dtSub == null || (dtMain.Rows.Count != dtSub.Length))
            {
                return("导出数据为空");
            }
            if (helpEntity.ExportColumns == null || helpEntity.ExportColumns.Any() == false)
            {
                return("配置数据为空");
            }
            var masterColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
            var subColumn    = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
            if (masterColumn == null || masterColumn.Any() == false)
            {
                return("主表配置数据为空");
            }
            if (subColumn == null || subColumn.Any() == false)
            {
                return("子表配置数据为空");
            }
            int maxColIndex = masterColumn.Select(t => t.ColIndex).Max();
            int minColIndex = masterColumn.Select(t => t.ColIndex).Min();
            if (minColIndex == maxColIndex)
            {
                return("主表配置列不能全在一个位置");
            }

            #endregion
            var temp = masterColumn.Select(t => t.RowIndex).Distinct();
            foreach (var rowIndex in temp)
            {
                List <int> colIndexs = masterColumn.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex).Select(t => t.ColIndex).Distinct().ToList();
                bool       result    = EqualDiif(colIndexs, 1);
                if (result == false)
                {
                    errorMessage = "列间距必须为1";
                    break;
                }
            }
            if (!string.IsNullOrEmpty(errorMessage))
            {
                return(errorMessage);
            }
            // 处理工作簿、文件名
            string            fileName    = BeforeDealSheet(helpEntity);
            ExportSheetEntity sheetEntity = null;
            for (int i = 0; i < dtMain.Rows.Count; i++)
            {
                var dtData = dtSub[i];
                sheetEntity = new ExportSheetEntity();
                if (i < helpEntity.SheetName.Count)
                {
                    sheetEntity.SheetName = helpEntity.SheetName[i];
                }
                if (i < helpEntity.SheetTitle.Count)
                {
                    sheetEntity.SheetTitle = helpEntity.SheetTitle[i];
                }
                BuildRecSheet(helpEntity, sheetEntity, dtMain.Rows[i], dtData);
            }

            // 写入Http
            HttpWrite(fileName);
            return(errorMessage);
        }
コード例 #7
0
        /// <summary>
        /// 导出标题合并类型
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="helpEntity"></param>
        /// <returns></returns>
        private string RunExportMerge(ExportRunEntity helpEntity, params DataTable[] dtData)
        {
            /** 模板类型:
             *  合并标题行                                   第5周                 第6周
             *  标题行 编号  名称  型号  采购周期  需求 总需求  即时库存  需求  总需求  即时库存
             *  内容行 xx     xx    xx     xx       xx    xx       xx      xx     xx       xx
             * */

            string errorMessage = string.Empty;

            if (dtData == null)
            {
                return("导出数据为空");
            }
            foreach (var dt in dtData)
            {
                if (dt == null || dt.Rows.Count == 0)
                {
                    errorMessage = "导出数据为空";
                    break;
                }
            }
            if (!string.IsNullOrEmpty(errorMessage))
            {
                return(errorMessage);
            }
            if (helpEntity.ExportColumns == null || helpEntity.ExportColumns.Any() == false)
            {
                return("导出配置为空");
            }

            IEnumerable <string> mergeNames = helpEntity.ExportColumns.Where(t => string.IsNullOrEmpty(t.MergeName) == false).Select(t => t.MergeName).Distinct();

            if (mergeNames == null || mergeNames.Any() == false)
            {
                return("合并标题为空");
            }
            // 处理工作簿、文件名
            string fileName = BeforeDealSheet(helpEntity);

            // 处理每个工作表数据
            ExportSheetEntity sheetEntity = null;

            for (int i = 0; i < dtData.Length; i++)
            {
                sheetEntity = new ExportSheetEntity();
                if (i < helpEntity.SheetName.Count)
                {
                    sheetEntity.SheetName = helpEntity.SheetName[i];
                }
                if (i < helpEntity.SheetTitle.Count)
                {
                    sheetEntity.SheetTitle = helpEntity.SheetTitle[i];
                }
                sheetEntity.MergeNames = mergeNames;
                errorMessage           = BuildMergeSheet(helpEntity, sheetEntity, dtData[i]);
                if (!string.IsNullOrEmpty(errorMessage))
                {
                    break;
                }
            }
            // 如果存在错误,则终止执行
            if (!string.IsNullOrEmpty(errorMessage))
            {
                return(errorMessage);
            }

            // 写入Http
            HttpWrite(fileName);

            return(errorMessage);
        }