private static void OutputDateRow(ISheet sheet, IEnumerable<string> distinctDates, int rowIndex)
        {
            var colSpan = 3;
            var startCol = 1;

            var dateRow = sheet.CreateRow(rowIndex);
            var headerRow = sheet.CreateRow(rowIndex + 1);

            dateRow.CreateCell(0).SetCellValue("");
            headerRow.CreateCell(0).SetCellValue("");

            for (var i = 0; i < distinctDates.Count(); i++)
            {
                var cell = dateRow.CreateCell(startCol);
                cell.SetCellValue(distinctDates.ElementAt(i));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startCol, colSpan * (i + 1)));

                headerRow.CreateCell(startCol).SetCellValue(ValueHeader);
                headerRow.CreateCell(startCol + 1).SetCellValue(PrefixHeader);
                headerRow.CreateCell(startCol + 2).SetCellValue(DlHeader);

                startCol = startCol + colSpan;
            }

        }
Example #2
0
 internal static void columnMerge(ISheet sheet, ICellStyle cellStyle, int row, int startColumn, int endColumn)
 {
     sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
     cellStyle.BorderBottom = BorderStyle.Thin;
     cellStyle.BorderTop = BorderStyle.Thin;
     cellStyle.BorderLeft = BorderStyle.Thin;
     cellStyle.BorderRight = BorderStyle.Thin;
 }
Example #3
0
        public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
        {
            if (sourceRowIndex == targetRowIndex)
            {
                throw new ArgumentException("sourceIndex and targetIndex cannot be same");
            }
            // Get the source / new row
            IRow newRow    = sheet.GetRow(targetRowIndex);
            IRow sourceRow = sheet.GetRow(sourceRowIndex);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
            }
            newRow        = sheet.CreateRow(targetRowIndex);
            newRow.Height = sourceRow.Height;   //copy row height

            // Loop through source columns to add to new row
            for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    continue;
                }
                ICell newCell = newRow.CreateCell(i);

                if (oldCell.CellStyle != null)
                {
                    // apply style from old cell to new cell
                    newCell.CellStyle = oldCell.CellStyle;
                }

                // If there is a cell comment, copy
                if (oldCell.CellComment != null)
                {
                    newCell.CellComment = oldCell.CellComment;
                }

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null)
                {
                    newCell.Hyperlink = oldCell.Hyperlink;
                }

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                case CellType.Blank:
                    newCell.SetCellValue(oldCell.StringCellValue);
                    break;

                case CellType.Boolean:
                    newCell.SetCellValue(oldCell.BooleanCellValue);
                    break;

                case CellType.Error:
                    newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                    break;

                case CellType.Formula:
                    newCell.SetCellFormula(oldCell.CellFormula);
                    break;

                case CellType.Numeric:
                    newCell.SetCellValue(oldCell.NumericCellValue);
                    break;

                case CellType.String:
                    newCell.SetCellValue(oldCell.RichStringCellValue);
                    break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
                if (cellRangeAddress != null && cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                                (newRow.RowNum +
                                                                                 (cellRangeAddress.LastRow - cellRangeAddress.FirstRow
                                                                                 )),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    sheet.AddMergedRegion(newCellRangeAddress);
                }
            }
            return(newRow);
        }
Example #4
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream Export()
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
        public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
        {
            int colint = 0;

            for (int i = 0; i < dtSource.Columns.Count;)
            {
                DataColumn column = dtSource.Columns[i];
                if (excelConfig.ColumnEntity[colint].Column != column.ColumnName)
                {
                    dtSource.Columns.Remove(column.ColumnName);
                }
                else
                {
                    i++;
                    colint++;
                }
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = "XXX";  //填加xls文件作者信息
                si.ApplicationName          = "卓软信息"; //填加xls文件创建程序信息
                si.LastAuthor               = "XXX";  //填加xls文件最后保存者信息
                si.Comments                 = "XXX";  //填加xls文件作者信息
                si.Title                    = "标题信息"; //填加xls文件标题信息
                si.Subject                  = "主题信息"; //填加文件主题信息
                si.CreateDateTime           = DateTimeHelper.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            #region 设置标题样式
            ICellStyle   headStyle      = workbook.CreateCellStyle();
            int[]        arrColWidth    = new int[dtSource.Columns.Count];
            string[]     arrColName     = new string[dtSource.Columns.Count];     //列名
            ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
            headStyle.Alignment = HorizontalAlignment.Center;                     // ------------------
            if (excelConfig.Background != new Color())
            {
                if (excelConfig.Background != new Color())
                {
                    headStyle.FillPattern         = FillPattern.SolidForeground;
                    headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
                }
            }
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig.TitlePoint;
            if (excelConfig.ForeColor != new Color())
            {
                font.Color = GetXLColour(workbook, excelConfig.ForeColor);
            }
            font.Boldweight = 700;
            headStyle.SetFont(font);
            #endregion

            #region 列头及样式
            ICellStyle cHeadStyle = workbook.CreateCellStyle();
            cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
            IFont cfont = workbook.CreateFont();
            cfont.FontHeightInPoints = excelConfig.HeadPoint;
            cHeadStyle.SetFont(cfont);
            #endregion

            #region 设置内容单元格样式
            foreach (DataColumn item in dtSource.Columns)
            {
                ICellStyle columnStyle = workbook.CreateCellStyle();
                columnStyle.Alignment     = HorizontalAlignment.Center;
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                arrColName[item.Ordinal]  = item.ColumnName.ToString();
                if (excelConfig.ColumnEntity != null)
                {
                    ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
                    if (columnentity != null)
                    {
                        arrColName[item.Ordinal] = columnentity.ExcelColumn;
                        if (columnentity.Width != 0)
                        {
                            arrColWidth[item.Ordinal] = columnentity.Width;
                        }
                        if (columnentity.Background != new Color())
                        {
                            if (columnentity.Background != new Color())
                            {
                                columnStyle.FillPattern         = FillPattern.SolidForeground;
                                columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
                            }
                        }
                        if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
                        {
                            IFont columnFont = workbook.CreateFont();
                            columnFont.FontHeightInPoints = 10;
                            if (columnentity.Font != null)
                            {
                                columnFont.FontName = columnentity.Font;
                            }
                            if (columnentity.Point != 0)
                            {
                                columnFont.FontHeightInPoints = columnentity.Point;
                            }
                            if (columnentity.ForeColor != new Color())
                            {
                                columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
                            }
                            columnStyle.SetFont(font);
                        }
                        columnStyle.Alignment = getAlignment(columnentity.Alignment);
                    }
                }
                arryColumStyle[item.Ordinal] = columnStyle;
            }
            if (excelConfig.IsAllSizeColumn)
            {
                #region 根据列中最长列的长度取得列宽
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        if (arrColWidth[j] != 0)
                        {
                            int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                            if (intTemp > arrColWidth[j])
                            {
                                arrColWidth[j] = intTemp;
                            }
                        }
                    }
                }
                #endregion
            }
            #endregion

            #region 填充数据

            #endregion
            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        if (excelConfig.Title != null)
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            if (excelConfig.TitleHeight != 0)
                            {
                                headerRow.Height = (short)(excelConfig.TitleHeight * 20);
                            }
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
                        }
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1);
                        #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
                            headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        #endregion
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);
                    newCell.CellStyle = arryColumStyle[column.Ordinal];
                    string drValue = row[column].ToString();
                    SetCell(newCell, dateStyle, column.DataType, drValue);
                }
                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }
Example #5
0
        public static MemoryStream Export(List <DataTable> dtSource, string strHeaderText, List <string> sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();


            for (int sheetIndex = 0; sheetIndex < sheetName.Count; sheetIndex++)
            {
                ISheet      sheet     = workbook.CreateSheet(sheetName[sheetIndex]);
                ICellStyle  dateStyle = workbook.CreateCellStyle();
                IDataFormat format    = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                //取得列宽
                int[] arrColWidth = new int[dtSource[sheetIndex].Columns.Count];
                foreach (DataColumn item in dtSource[sheetIndex].Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < dtSource[sheetIndex].Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource[sheetIndex].Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[sheetIndex].Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }

                int rowIndex = 0;

                if (dtSource[sheetIndex].Rows.Count == 0)
                {
                    try
                    {
                        #region 新建表,填充表头,填充列头,样式
                        if (rowIndex == 65535 || rowIndex == 0)
                        {
                            bool hasHeader = false;
                            if (rowIndex != 0)
                            {
                                sheet = workbook.CreateSheet();
                            }

                            #region 表头及样式
                            if (!string.IsNullOrWhiteSpace(strHeaderText))
                            {
                                hasHeader = true;
                                IRow headerRow = sheet.CreateRow(0);
                                headerRow.HeightInPoints = 25;
                                headerRow.CreateCell(0).SetCellValue(sheetName[sheetIndex] + strHeaderText);

                                ICellStyle headStyle = workbook.CreateCellStyle();
                                headStyle.Alignment = HorizontalAlignment.Center;
                                IFont font = workbook.CreateFont();
                                font.FontHeightInPoints = 12;
                                font.Boldweight         = 500;
                                headStyle.SetFont(font);

                                headerRow.GetCell(0).CellStyle = headStyle;

                                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[sheetIndex].Columns.Count - 1));
                                //headerRow.Dispose();
                                //headerRow.d
                            }
                            #endregion

                            #region 列头及样式
                            {
                                IRow headerRow = sheet.CreateRow(hasHeader?1:0);

                                ICellStyle headStyle = workbook.CreateCellStyle();
                                headStyle.Alignment = HorizontalAlignment.Center;
                                IFont font = workbook.CreateFont();
                                font.FontHeightInPoints = 10;
                                font.Boldweight         = 700;
                                headStyle.SetFont(font);

                                foreach (DataColumn column in dtSource[sheetIndex].Columns)
                                {
                                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                    //设置列宽
                                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                                }
                                //headerRow.Dispose();
                            }
                            #endregion

                            rowIndex = hasHeader?2:1;
                        }
                        #endregion
                    }
                    catch
                    { }
                }
                else
                {
                    foreach (DataRow row in dtSource[sheetIndex].Rows)
                    {
                        #region 新建表,填充表头,填充列头,样式
                        if (rowIndex == 65535 || rowIndex == 0)
                        {
                            bool hasHeader = false;
                            if (rowIndex != 0)
                            {
                                sheet = workbook.CreateSheet();
                            }

                            #region 表头及样式

                            if (!string.IsNullOrWhiteSpace(strHeaderText))
                            {
                                hasHeader = true;
                                IRow headerRow = sheet.CreateRow(0);
                                headerRow.HeightInPoints = 25;
                                headerRow.CreateCell(0).SetCellValue(sheetName[sheetIndex] + strHeaderText);

                                ICellStyle headStyle = workbook.CreateCellStyle();
                                headStyle.Alignment = HorizontalAlignment.Center;
                                IFont font = workbook.CreateFont();
                                font.FontHeightInPoints = 12;
                                font.Boldweight         = 700;
                                headStyle.SetFont(font);

                                headerRow.GetCell(0).CellStyle = headStyle;

                                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[sheetIndex].Columns.Count - 1));
                                //headerRow.Dispose();
                                //headerRow.d
                            }
                            #endregion

                            #region 列头及样式
                            {
                                IRow headerRow = sheet.CreateRow(hasHeader?1:0);

                                ICellStyle headStyle = workbook.CreateCellStyle();
                                headStyle.Alignment = HorizontalAlignment.Center;
                                IFont font = workbook.CreateFont();
                                font.FontHeightInPoints = 10;
                                font.Boldweight         = 700;
                                headStyle.SetFont(font);

                                foreach (DataColumn column in dtSource[sheetIndex].Columns)
                                {
                                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                                    //设置列宽
                                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                                }
                                //headerRow.Dispose();
                            }
                            #endregion

                            rowIndex = hasHeader?2:1;
                        }
                        #endregion


                        #region 填充内容
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        foreach (DataColumn column in dtSource[sheetIndex].Columns)
                        {
                            ICell newCell = dataRow.CreateCell(column.Ordinal);

                            string drValue = row[column].ToString();

                            switch (column.DataType.ToString())
                            {
                            case "System.String":    //字符串类型
                                newCell.SetCellValue(drValue);
                                break;

                            case "System.DateTime":    //日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);

                                newCell.CellStyle = dateStyle;    //格式化显示
                                break;

                            case "System.Boolean":    //布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;

                            case "System.Int16":    //整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;

                            case "System.Decimal":    //浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;

                            case "System.DBNull":    //空值处理
                                newCell.SetCellValue("");
                                break;

                            default:
                                newCell.SetCellValue("");
                                break;
                            }
                        }
                        #endregion

                        rowIndex++;
                    }
                }
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                workbook    = null;
                return(ms);
            }
        }
    /// <summary>
    /// 为Excel添加数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="gv"></param>
    /// <param name="colTypeList">GridView每一列的数据类型</param>
    /// <param name="colCount">GridView的总列数</param>
    /// <param name="rowInex">添加Excel数据行的起始索引号</param>
    /// <param name="cellStyle">表格基础格式</param>
    private void AddSheetBody(ISheet sheet, GridView gv, ICellStyle cellStyle, List<int> colTypeList, int colCount, int rowInex)
    {
        IRow row;
        ICell cell;
        ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
        ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);

        int rowCount = gv.Rows.Count;

        for (int i = 0; i < rowCount; i++)
        {
            row = sheet.CreateRow(rowInex);

            for (int j = 0; j < colCount; j++)
            {
                if (gv.Rows[i].Cells[j].Visible == false) continue;

                string cellText = gv.Rows[i].Cells[j].Text.Trim();
                cellText = cellText.Replace("&nbsp;", "");//替换空字符占位符
                cellText = cellText.Replace("&gt;", ">");//替换 > 占位符

                if (string.IsNullOrEmpty(cellText)) continue;//单元格为空跳过

                cell = row.CreateCell(j);
                if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型
                {
                    cell.SetCellValue(cellText);
                    cell.CellStyle = cellStyle;
                }
                else
                {
                    try
                    {
                        switch (colTypeList[j])
                        {
                            case 1: cell.SetCellValue(int.Parse(cellText));//int类型
                                cell.CellStyle = cellStyle;
                                break;
                            case 2: cell.SetCellValue(double.Parse(cellText));//decimal数据类型
                                cell.CellStyle = cellStyleDecimal;
                                break;
                            case 3: cell.SetCellValue(DateTime.Parse(cellText));//日期类型
                                cell.CellStyle = cellStyleDateTime;
                                break;
                            default: cell.SetCellValue(cellText);
                                cell.CellStyle = cellStyle;
                                break;
                        }
                    }
                    catch
                    {
                        cell.SetCellValue("单元格导出失败");
                        MCSFramework.Common.LogWriter.FILE_PATH = GetAttachmentDirectory();
                        MCSFramework.Common.LogWriter.WriteLog("\r\n第j=" + j + "类发生错误,数据类型为" + colTypeList[j].ToString() + ",数值为" + cellText + ",报表GUID=" + Request.QueryString["Report"] != null ? Request.QueryString["Report"] : "无GUID值" + "\r\n");
                    }
                }

                int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan > 0 ? gv.Rows[i].Cells[j].ColumnSpan - 1 : 0;//跨列,即合并的列数

                int MergeDown = gv.Rows[i].Cells[j].RowSpan > 0 ? gv.Rows[i].Cells[j].RowSpan - 1 : 0;//跨行,即合并的行数

                if (MergeAcross > 0 || MergeDown > 0)//存在要合并的行
                {
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + MergeDown, j, j + MergeAcross));
                    j += MergeAcross;
                }
            }
            rowInex++;
        }
    }
Example #7
0
            private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
            {
                var rn0 = ws.PhysicalNumberOfRows;
                var rn = rn0;
                for (var i = 0; i < names.Length; ++i)
                {
                    var rnc = rn++;
                    var r = ws.CreateRow(rnc);
                    if (1 < names[i].Length)
                    {
                        r.RowStyle = cs3;
                        var vh = ws.GetDataValidationHelper();
                        var vl = names[i].Skip(1).ToList();
                        vl.Sort();
                        var vd = vh.CreateValidation(
                            vh.CreateExplicitListConstraint(vl.ToArray()),
                            new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
                            );
                        vd.ShowErrorBox = false;
                        vd.ShowPromptBox = true;
                        ws.AddValidationData(vd);
                    }

                    var c = r.CreateCell(0);
                    c.SetCellValue(names[i][0]);
                    if (0 == i)
                    {
                        c.CellStyle = cs1;
                        r.RowStyle = cs1;
                    }
                    else
                    {
                        c.CellStyle = cs2;
                    }
                }
                if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
                //ws.SetRowGroupCollapsed(rn0 + 1, true);
                ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
            }
Example #8
0
        private void MergeColum(List<StockModel> stockModels, ISheet sheet)
        {
            var preModel = stockModels[0];
            int row_start = 2;
            int row_end = 2;

            for (int i = 1; i < stockModels.Count; i++)
            {
                if (preModel.Type == stockModels[i].Type)
                {
                    row_end++;
                }
                else
                {
                    if (row_end > row_start)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));

                        row_start = row_end;
                    }
                    row_end++;
                    row_start++;
                }

                preModel = stockModels[i];
            }

            if (row_end > row_start)
            {
                sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
            }
        }
Example #9
0
        public FileResult ExportQuestionary(string QuestionaryID)
        {
            CellRangeAddress cellRange;
            //创建Excel文件的对象
            HSSFWorkbook xSSFWorkbook = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = xSSFWorkbook.CreateSheet("Sheet1");
            //获取list数据
            List <Question>   questions   = EFQuestionary.Questions.Where(q => q.QuestionaryID == QuestionaryID).OrderBy(q => q.QuestionID).ToList();
            List <Select>     selects     = EFQuestionary.Selects.ToList();
            List <FillOut>    fillOuts    = EFQuestionary.FillOuts.Where(f => f.QuestionaryID == QuestionaryID).OrderBy(f => f.GameID).ToList();
            List <GameMember> gameMembers = EFGameMember.gameMembers.ToList();
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);

            row1.CreateCell(0).SetCellValue("游戏ID");
            row1.CreateCell(1).SetCellValue("游戏昵称");
            int count = 2;

            foreach (var item in questions)
            {
                row1.CreateCell(count).SetCellValue(item.QuestionContext);
                cellRange = new CellRangeAddress(0, 0, count, count + selects.Where(s => s.QuestionID == item.QuestionID).Count() - 1);
                sheet1.AddMergedRegion(cellRange);
                count += selects.Where(s => s.QuestionID == item.QuestionID).Count();
            }
            //将数据逐步写入sheet1各个行
            IRow row2 = sheet1.CreateRow(1);

            row2.CreateCell(0).SetCellValue("选项值");
            cellRange = new CellRangeAddress(1, 1, 0, 1);
            sheet1.AddMergedRegion(cellRange);
            count = 2;
            foreach (var item in questions)
            {
                foreach (var items in selects.Where(s => s.QuestionID == item.QuestionID).OrderBy(s => s.SelectID))
                {
                    if (items.Type)
                    {
                        row2.CreateCell(count++).SetCellValue("其他");
                    }
                    else
                    {
                        row2.CreateCell(count++).SetCellValue(items.Value);
                    }
                }
            }
            count = 2;
            int counts;

            foreach (var GameID in fillOuts.Select(f => f.GameID).Distinct())
            {
                counts = 0;
                IRow row = sheet1.CreateRow(count++);
                row.CreateCell(counts++).SetCellValue(GameID);
                row.CreateCell(counts++).SetCellValue(gameMembers.Where(g => g.GameID == GameID).FirstOrDefault().GameName.ToString());
                foreach (var item in questions)
                {
                    foreach (var items in selects.Where(s => s.QuestionID == item.QuestionID).OrderBy(s => s.SelectID))
                    {
                        FillOut fillOut = fillOuts.Where(f => f.GameID == GameID && f.SelectID == items.SelectID).FirstOrDefault();
                        if (fillOut != null)
                        {
                            if (items.Type)
                            {
                                row.CreateCell(counts++).SetCellValue(fillOut.Value);
                            }
                            else
                            {
                                row.CreateCell(counts++).SetCellValue("√");
                            }
                        }
                        else
                        {
                            row.CreateCell(counts++).SetCellValue("×");
                        }
                    }
                }
            }

            // 写入到客户端
            MemoryStream ms = new MemoryStream();

            xSSFWorkbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", "编号" + QuestionaryID + "调查问卷结果.xls"));
        }
Example #10
0
        /// <summary>
        /// 将错误信息写回原始存档中,并返回文件流。
        /// </summary>
        public static MemoryStream SetError(MDataTable dt, bool isReturnStream = true)
        {
            if (dt != null && dt.Columns.Contains("错误信息") && dt.DynamicData != null && dt.DynamicData is ISheet)
            {
                try
                {
                    ISheet     sheet        = dt.DynamicData as ISheet;
                    IWorkbook  workBook     = sheet.Workbook;
                    ICellStyle style        = GetStyle(workBook, HSSFColor.Red.Index);
                    int        dataRowStart = dt.RecordsAffected;

                    IRow excelRow = null;
                    excelRow = sheet.GetRow(0);//处理列头的前面有垃圾数据导致列头比下面的列头少的情况
                    IRow dataRow  = sheet.GetRow(dataRowStart - 1);
                    int  firstRow = 0;
                    if (excelRow.LastCellNum < dataRow.LastCellNum)
                    {
                        excelRow = dataRow;
                        firstRow = dataRowStart - 1;
                    }
                    //int cellCount = excelRow.Cells.Count;
                    int cellCount;
                    if (!int.TryParse(dt.Conn, out cellCount))
                    {
                        cellCount = excelRow.LastCellNum; //修复左上角第一个单元格为空时,错误信息错位的问题
                    }
                    if (cellCount > 255)                  //如果出现256列(后面全是空格列)
                    {
                        cellCount = dt.Columns.Count;
                    }

                    ICell errorCell = excelRow.CreateCell(cellCount, CellType.String);
                    sheet.SetColumnWidth(cellCount, 100 * 256);
                    if (dataRowStart > 1)
                    {
                        //添加错误信息列
                        CellRangeAddress cellAddress = new CellRangeAddress(firstRow, dataRowStart - 1, cellCount, cellCount);
                        sheet.AddMergedRegion(cellAddress);
                    }
                    errorCell.SetCellValue("错误信息");
                    bool hasError       = false;
                    int  errColumnIndex = dt.Columns.GetIndex("错误信息");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MDataRow dtRow = dt.Rows[i];
                        string   value = dtRow[errColumnIndex].ToString();
                        if (!string.IsNullOrEmpty(value))
                        {
                            IRow row = sheet.GetRow(i + dataRowStart);
                            if (row == null)
                            {
                                break;
                            }
                            hasError = true;
                            ICell cell = row.CreateCell(cellCount, CellType.String);
                            cell.SetCellValue(value);
                            cell.CellStyle = style;
                            for (int j = 0; j < dt.Columns.Count; j++) //标识错误的格的背景色为红色。
                            {
                                if (dtRow[j].State == -1)              // && j < row.Cells.Count
                                {
                                    cell = row.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                                    if (cell != null)
                                    {
                                        cell.CellStyle = style;
                                    }
                                }
                            }
                        }
                    }
                    if (hasError && isReturnStream)
                    {
                        MemoryStream ms = new MemoryStream();
                        workBook.Write(ms);
                        return(ms);
                    }
                    return(null);
                }
                catch (Exception err)
                {
                    Log.WriteLogToTxt(err);
                }
            }
            return(null);
        }
Example #11
0
 public static void columnMerge(ISheet sheet, int row, int startColumn, int endColumn, bool center)
 {
     sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
 }
Example #12
0
 /// <summary>
 /// 合并单元格
 /// </summary>
 /// <param name="sheet">要合并单元格所在的sheet</param>
 /// <param name="rowstart">开始行的索引</param>
 /// <param name="rowend">结束行的索引</param>
 /// <param name="colstart">开始列的索引</param>
 /// <param name="colend">结束列的索引</param>
 public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
 {
     NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(rowstart, rowend, colstart, colend);
     sheet.AddMergedRegion(cellRangeAddress);
 }
 /// <summary>
 /// 合并单元格
 /// </summary>
 /// <param name="sheet"> 要合并单元格所在的sheet </param>
 /// <param name="rowstart"> 开始行的索引 </param>
 /// <param name="rowend"> 结束行的索引 </param>
 /// <param name="colstart"> 开始列的索引 </param>
 /// <param name="colend"> 结束列的索引 </param>
 protected void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
 {
     CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
     sheet.AddMergedRegion(cellRangeAddress);
     // sheet.SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
 }
    /// <summary>
    /// 为Excel添加表头
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="headerRow">GridView的HeaderRow属性</param>
    /// <param name="headerCellStyle">表头格式</param>
    /// <param name="flagNewLine">转行标志</param>
    /// <param name="colCount">Excel表列数</param>
    /// <returns>Excel表格行数</returns>
    private int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount)
    {
        //int
        colCount = 0;//记录GridView列数
        int rowInex = 0;//记录表头的行数

        IRow row = sheet.CreateRow(0);
        ICell cell;

        int groupCount = 0;//记录分组数
        int colIndex = 0;//记录列索引,并于结束表头遍历后记录总列数
        for (int i = 0; i < headerRow.Cells.Count; i++)
        {
            if (rowInex != groupCount)//新增了标题行时重新创建
            {
                row = sheet.CreateRow(rowInex);
                groupCount = rowInex;
            }

            #region 是否跳过当前单元格

            for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
            {
                NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
                //当前单元格是处于合并区域内
                if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex
                    && a.FirstRow <= rowInex && a.LastRow >= rowInex)
                {
                    colIndex++;
                    m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
                }
            }

            #endregion

            cell = row.CreateCell(colIndex);
            cell.CellStyle = headerCellStyle;

            TableCell tablecell = headerRow.Cells[i];

            //跨列属性可能为添加了html属性colspan,也可能是由cell的ColumnSpan属性指定
            int colSpan = 0;
            int rowSpan = 0;

            #region 获取跨行跨列属性值
            //跨列
            if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
            {
                colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
                colSpan--;
            }
            if (tablecell.ColumnSpan > 1)
            {
                colSpan = tablecell.ColumnSpan;
                colSpan--;
            }

            //跨行
            if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
            {
                rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
                rowSpan--;
            }
            if (tablecell.RowSpan > 1)
            {
                rowSpan = tablecell.RowSpan;
                rowSpan--;
            }
            #endregion

            //添加excel合并区域
            if (colSpan > 0 || rowSpan > 0)
            {
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
                colIndex += colSpan + 1;//重新设置列索引
            }
            else
            {
                colIndex++;
            }
            string strHeader = headerRow.Cells[i].Text;

            if (strHeader.Contains(flagNewLine))//换行标记,当只存在一行标题时不存在</th></tr><tr>,此时colCount无法被赋值
            {
                rowInex++;
                colCount = colIndex;
                colIndex = 0;

                strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
            }
            cell.SetCellValue(strHeader);
        }
        if (groupCount == 0)//只有一行标题时另外为colCount赋值
        {
            colCount = colIndex;
        }
        rowInex++;//表头结束后另起一行开始记录控件数据行索引

        return rowInex;
    }
Example #15
0
        private ISheet createSheet1(HSSFWorkbook workBook, string sheetName, ExcelModelAttribute ema)
        {
            ISheet sheet   = workBook.CreateSheet(sheetName);
            IRow   RowHead = sheet.CreateRow(0);

            //合并第一行单元格
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ema.excelNoticeCols));

            RowHead.CreateCell(0).SetCellValue(ema.excelNotice);
            /*****单元格样式 start******/
            var cellStyleHead = workBook.CreateCellStyle();

            cellStyleHead.Alignment         = HorizontalAlignment.Center; //居中显示
            cellStyleHead.VerticalAlignment = VerticalAlignment.Top;      //垂直居中
            cellStyleHead.WrapText          = true;
            //高度
            RowHead.HeightInPoints = 50; //2 * sheet.DefaultRowHeight / 10;
            //字体设置,字体要调用CreateFont()
            IFont fonthead = workBook.CreateFont();

            fonthead.FontHeightInPoints = 10;
            fonthead.Color = HSSFColor.OliveGreen.Red.Index;
            cellStyleHead.SetFont(fonthead);
            //这里调试出来的,样式一定要给到单元格才有效
            RowHead.Cells[0].CellStyle = cellStyleHead;
            /*****单元格样式 end******/
            /*********添加表头 s********/
            IRow RowBody = sheet.CreateRow(1);

            for (int iColumnIndex = 0; iColumnIndex < ema.tableHeadList.Count(); iColumnIndex++)
            {
                RowBody.CreateCell(iColumnIndex).SetCellValue(ema.tableHeadList[iColumnIndex].ToString());
                RowBody.Cells[iColumnIndex].Row.HeightInPoints = 20;
                //单元格样式
                ICellStyle cellStyle = workBook.CreateCellStyle();
                cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                cellStyle.FillPattern         = FillPattern.SolidForeground;
                cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                //设置单元格上下左右边框线
                cellStyle.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
                //文字水平和垂直对齐方式
                cellStyle.Alignment         = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Top;
                //是否换行
                cellStyle.WrapText = true;

                RowBody.Cells[iColumnIndex].CellStyle = cellStyle;
                //字体大小
                IFont cellfont = workBook.CreateFont();
                cellfont.FontHeightInPoints = 14;
                cellStyle.SetFont(cellfont);
                sheet.SetColumnWidth(iColumnIndex, 20 * 256);
            }
            /*********添加表头 e********/
            //设置下拉框
            setSheet2(workBook, sheet, ema);
            return(sheet);
        }
Example #16
0
        /// <summary>
        /// 根据传入数据新建sheet表单到指定workbook
        /// </summary>
        /// <param name="objectDatas"></param>
        /// <param name="excelHeader"></param>
        /// <param name="sheetName"></param>
        /// <param name="regulars"></param>
        /// <param name="workbook"></param>
        private static void CreateExcelSheetByDatas <T>(List <T> objectDatas, string excelHeader, string sheetName, ref IWorkbook workbook, string xmlPath)
        {
            var regulars = GetExportRegulars(xmlPath);

            // excel sheet表单
            ISheet sheet = workbook.CreateSheet(sheetName);
            // excel行数
            int rows = 0;

            #region 单元格 -表头格式

            #region 表头字体

            IFont fontTitle = workbook.CreateFont();
            fontTitle.FontHeightInPoints = 12;
            fontTitle.Boldweight         = (short)FontBoldWeight.Bold;

            #endregion

            ICellStyle styleTitle = workbook.CreateCellStyle();
            styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            styleTitle.SetFont(fontTitle);
            styleTitle.VerticalAlignment = VerticalAlignment.Center;
            //styleTitle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; //XSSFColor
            //styleTitle.FillPattern = FillPattern.SolidForeground;
            #endregion

            #region 单元格 -表体格式

            #region 表体字体

            IFont fontMessage = workbook.CreateFont();
            fontMessage.FontHeightInPoints = 10;

            #endregion

            ICellStyle styleMessage = workbook.CreateCellStyle();
            styleMessage.Alignment = HorizontalAlignment.Center;
            styleMessage.SetFont(fontMessage);
            styleMessage.VerticalAlignment = VerticalAlignment.Center;

            #endregion
            if (!string.IsNullOrEmpty(excelHeader))//表头存在
            {
                // 创建表头并赋值  导出的对象列数为表头的合并列数
                int  firstRowCellCount = regulars.Count;//GetAttributeCount(objectDatas.First());
                IRow headerRow         = sheet.CreateRow(rows);
                headerRow.HeightInPoints = 40;
                var headerCell = headerRow.CreateCell(0);
                headerCell.SetCellValue(excelHeader);

                // 合并表头
                var cellRangeAddress = new CellRangeAddress(rows, rows, 0, firstRowCellCount - 1);
                sheet.AddMergedRegion(cellRangeAddress);
                // 设置表头格式
                headerCell.CellStyle = styleTitle;
                rows++;
            }
            //生成表头(属性表头)
            if (objectDatas.Any())
            {
                // excel列数
                int cells = -1;
                // 创建数据行
                var firstRow = sheet.CreateRow(rows);
                firstRow.HeightInPoints = 25;
                var objectData = objectDatas.FirstOrDefault();
                foreach (System.Reflection.PropertyInfo p in objectData.GetType().GetProperties())
                {
                    var regular = regulars.Find(t => t.PropertyName == p.Name);
                    if (regular != null)
                    {
                        cells++;
                        //throw new Exception("导出excel时,出现未配置字段。表:" + objectData.GetType().Name + ",字段:" + p.Name);
                        var firstRowCell = firstRow.CreateCell(cells);
                        firstRowCell.SetCellValue(regular.ExportFieldName);
                        sheet.SetColumnWidth(cells, regular.ExportFieldName.Length * 256 * 4);
                        firstRowCell.CellStyle = styleMessage;
                    }
                }
            }

            // 反射object对象,遍历字段
            foreach (var objectData in objectDatas)
            {
                rows++;
                // excel列数
                int cells = -1;
                // 创建数据行
                var messageRow = sheet.CreateRow(rows);
                messageRow.HeightInPoints = 16;
                foreach (PropertyInfo p in objectData.GetType().GetProperties())
                {
                    var regular = regulars.Find(t => t.PropertyName == p.Name);
                    if (regular != null)
                    {
                        cells++;
                        var messageCell = messageRow.CreateCell(cells);
                        var value       = p.GetValue(objectData);
                        if (value == null)
                        {
                            messageCell.SetCellValue("");
                        }
                        else
                        {
                            switch (regular.DataType)
                            {
                            case "DateTime":
                                if (Convert.ToDateTime(value) == DateTime.MinValue)
                                {
                                    messageCell.SetCellValue("");
                                }
                                else
                                {
                                    messageCell.SetCellValue(
                                        Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                break;

                            case "Int":
                                messageCell.SetCellValue(Convert.ToInt32(value));
                                break;

                            case "Double":
                                messageCell.SetCellValue(Convert.ToDouble(value));
                                break;

                            case "Decimal":
                                messageCell.SetCellValue(Convert.ToDouble(value));
                                break;

                            case "Bool":
                                var setValue = "是";
                                if (!(bool)value)
                                {
                                    setValue = "否";
                                }
                                messageCell.SetCellValue(setValue);
                                break;

                            default:
                                messageCell.SetCellValue(value.ToString());
                                break;
                            }
                        }
                        messageCell.CellStyle = styleMessage;
                    }
                }
            }
        }
Example #17
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, DocumentSummaryInformation dsi, SummaryInformation si)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            if (dsi != null)
            {
                workbook.DocumentSummaryInformation = dsi;
            }
            if (si != null)
            {
                workbook.SummaryInformation = si;
            }

            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();

            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell  newCell = dataRow.CreateCell(column.Ordinal);
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                    case "System.String":    //字符串类型
                        newCell.SetCellValue(drValue);
                        break;

                    case "System.DateTime":    //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle;    //格式化显示
                        break;

                    case "System.Boolean":    //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case "System.Int16":    //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case "System.Decimal":    //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case "System.DBNull":    //空值处理
                        newCell.SetCellValue("");
                        break;

                    default:
                        newCell.SetCellValue("");
                        break;
                    }
                }
                #endregion

                rowIndex++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                return(ms);
            }
        }
Example #18
0
        public static bool ExportExcel(DataTable dt, string filePath, bool bMerge = false)
        {
            bool bRes = false;

            try
            {
                if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
                {
                    HSSFWorkbook book  = new HSSFWorkbook();
                    ISheet       sheet = book.CreateSheet(dt.TableName);

                    IRow row = sheet.CreateRow(0);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(dt.Columns[i].Caption);
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row2 = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                        }
                    }
                    //列宽自适应,只对英文和数字有效
                    for (int i = 0; i <= dt.Rows.Count; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                    //获取当前列的宽度,然后对比本列的长度,取最大值
                    for (int columnNum = 0; columnNum <= dt.Rows.Count; columnNum++)
                    {
                        int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                        for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                        {
                            IRow currentRow;
                            //当前行未被使用过
                            if (sheet.GetRow(rowNum) == null)
                            {
                                currentRow = sheet.CreateRow(rowNum);
                            }
                            else
                            {
                                currentRow = sheet.GetRow(rowNum);
                            }

                            if (currentRow.GetCell(columnNum) != null)
                            {
                                ICell currentCell = currentRow.GetCell(columnNum);
                                int   length      = System.Text.Encoding.Default.GetBytes(currentCell.ToString()).Length;
                                if (columnWidth < length)
                                {
                                    columnWidth = length;
                                }
                            }
                        }
                        sheet.SetColumnWidth(columnNum, columnWidth * 256);
                    }
                    if (bMerge)
                    {
                        HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
                        cellStyle.VerticalAlignment = VerticalAlignment.Center;

                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            int    indexs = 0;
                            int    indexe = 0;
                            string vtemp  = dt.Rows[0][i].ToString();
                            for (int j = 0; j < dt.Rows.Count - 1; j++)
                            {
                                string temp = dt.Rows[j + 1][i].ToString();
                                if (temp != vtemp)
                                {
                                    indexe = j + 1;
                                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexs + 1, indexe, i, i));
                                    ICell cell = sheet.GetRow(indexs + 1).GetCell(i);
                                    if (cell != null)
                                    {
                                        cell.CellStyle = cellStyle;
                                    }
                                    indexs = j + 1;
                                    vtemp  = temp;
                                }
                            }
                            if (indexe < dt.Rows.Count)
                            {
                                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexs + 1, dt.Rows.Count, i, i));
                                ICell cell = sheet.GetRow(indexs + 1).GetCell(i);
                                if (cell != null)
                                {
                                    cell.CellStyle = cellStyle;
                                }
                            }
                        }
                    }
                    // 写入到客户端
                    using (MemoryStream ms = new MemoryStream())
                    {
                        book.Write(ms);
                        using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                        {
                            byte[] data = ms.ToArray();
                            fs.Write(data, 0, data.Length);
                            fs.Flush();
                        }
                        book = null;
                    }
                    bRes = true;
                }
            }
            catch (Exception ex)
            {
                bRes = false;
            }
            return(bRes);
        }
Example #19
0
        static void FillTheSoSheet(ISheet sheet,So so, List<SoItemsContentAndState> soitemList)
        {
            sheet.GetRow(0).CreateCell(2).SetCellValue(so.customerName);
               sheet.GetRow(0).CreateCell(6).SetCellValue(so.contact);
               sheet.GetRow(0).CreateCell(9).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById(so.salesId));

               if (so.approverId != null)
               {
               sheet.GetRow(0).CreateCell(14).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById(so.approverId.Value) + "," + so.approveDate.Value.ToShortDateString());
               }
               sheet.GetRow(1).CreateCell(2).SetCellValue(so.salesOrderNo);
               sheet.GetRow(1).CreateCell(6).SetCellValue(so.orderDate.ToShortDateString());

               sheet.GetRow(1).CreateCell(9).SetCellValue(so.customerPo);
               sheet.GetRow(1).CreateCell(14).SetCellValue(so.paymentTerm);

               sheet.GetRow(2).CreateCell(2).SetCellValue(so.freightTerm);
               sheet.GetRow(2).CreateCell(6).SetCellValue(so.customerAccount);
               sheet.GetRow(2).CreateCell(9).SetCellValue(so.specialInstructions);

               IRow row = sheet.CreateRow(4);
               row.CreateCell(0).SetCellValue(so.billTo);
               row.CreateCell(8).SetCellValue(so.shipTo);

               int itemRowIndex = 9;
               foreach (SoItemsContentAndState scs in soitemList)
               {
               IRow itemRow = sheet.CreateRow(itemRowIndex);
               itemRow.CreateCell(0).SetCellValue(soitemList.IndexOf(scs)+1);
               string strSaleType;
               switch (scs.soitem.saleType)
               {

                   case 0:

                       strSaleType = "OEM EXCESS";
                       break;
                   case 1:
                       strSaleType = "OWN STOCK";
                       break;
                   case 2:
                       strSaleType = "OTHERS";
                       break;
                   default:
                       strSaleType = "ERROR";
                       break;

               }

               itemRow.CreateCell(1).SetCellValue(strSaleType);
               itemRow.CreateCell(2).SetCellValue(scs.soitem.partNo);
               itemRow.CreateCell(3).SetCellValue(scs.soitem.mfg);
               itemRow.CreateCell(4).SetCellValue(scs.soitem.rohs == 1 ? "Y" : "N");
               itemRow.CreateCell(5).SetCellValue(scs.soitem.dc);
               itemRow.CreateCell(6).SetCellValue(scs.soitem.intPartNo);
               itemRow.CreateCell(7).SetCellValue(scs.soitem.shipFrom);
               itemRow.CreateCell(8).SetCellValue(scs.soitem.shipMethod);
               itemRow.CreateCell(9).SetCellValue(scs.soitem.trackingNo);
               itemRow.CreateCell(10).SetCellValue(scs.soitem.qty);
               if (scs.soitem.qtyshipped != null)
               {
                   itemRow.CreateCell(11).SetCellValue(scs.soitem.qtyshipped.Value);
               }
               itemRow.CreateCell(12).SetCellValue(Enum.GetName(typeof(AmbleClient.Currency), scs.soitem.currencyType));
               itemRow.CreateCell(13).SetCellValue(scs.soitem.unitPrice);
               itemRow.CreateCell(14).SetCellValue(scs.soitem.unitPrice * scs.soitem.qty);
               itemRow.CreateCell(15).SetCellValue(scs.soitem.dockDate.ToShortDateString());

               if (scs.soitem.shippedDate != null)
               {
                   itemRow.CreateCell(16).SetCellValue(scs.soitem.shippedDate.Value.ToShortDateString());
               }
               itemRowIndex++;

               IRow infoRow=sheet.CreateRow(itemRowIndex);
               infoRow.CreateCell(1).SetCellValue("Shipping Instructions >>" + scs.soitem.shippingInstruction);
               sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,1,16));

               itemRowIndex++;
               infoRow=sheet.CreateRow(itemRowIndex);
               infoRow.CreateCell(1).SetCellValue("Packing Instructions >>" + scs.soitem.packingInstruction);
               sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,1,16));
               itemRowIndex++;
               }
        }
Example #20
0
 private void Merge(int row, int column, int rowspan, int colspan)
 {
     _sheet.AddMergedRegion(new CellRangeAddress(row, row + rowspan - 1, column, column + colspan - 1));
 }
Example #21
0
        private int MSWriteToSheet1(Dictionary<int, SelfNPOICell[]> data, ISheet sheet)
        {
            if (data == null || data.Count == 0)
                return -1;

            List<int> setRow = new List<int>();
            setRow.Add(3);
            setRow.Add(4);
            setRow.Add(5);
            setRow.Add(6);
            setRow.Add(7);

            ICellStyle fontBold = this.setFontBold();
            ICellStyle cellBorder = this.setCellBorder();

            foreach (var r in data)
            {
                IRow row = sheet.CreateRow(r.Key);

                for (int col = 0; col < r.Value.Count(); col++)
                {
                    ICell cell = row.CreateCell(col);
                    cell.SetCellValue(r.Value[col].CellValue);
                    ////给加盟费等标签字体加粗
                    if (r.Value[col].IsFontBold)
                    {
                        cell.CellStyle = fontBold;
                    }

                    if (r.Value[col].HasCellBorder)
                    {
                        cell.CellStyle = cellBorder;
                    }
                }
                ////合并单元格并设置边框
                if (setRow.Contains(r.Key))
                {
                    CellRangeAddress region1 = new CellRangeAddress(r.Key, r.Key, 1, 2);
                    CellRangeAddress region2 = new CellRangeAddress(r.Key, r.Key, 5, 6);
                    sheet.AddMergedRegion(region1);
                    sheet.AddMergedRegion(region2);
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
                }
            }
            //设置合并单元格的长度,需全部显示文字
            sheet.SetColumnWidth(2, 30 * 256);
            sheet.SetColumnWidth(6, 30 * 256);

            return 0;
        }
Example #22
0
        //Datatable导出Excel
        public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            try
            {
                ISheet     sheet           = workbook.CreateSheet("Sheet1");
                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment    = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //标题头
                int icolIndex           = 0;
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0);
                sheet.AddMergedRegion(region);
                ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 20;
                ICell celltitle = headerRow.CreateCell(0);
                celltitle.SetCellValue(title);
                celltitle.CellStyle = HeadercellStyle;

                //用column name 作为列名
                IRow headerRow1 = sheet.CreateRow(1);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell1 = headerRow1.CreateCell(icolIndex);
                    cell1.SetCellValue(item.ColumnName);
                    cell1.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat   = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex  = 2;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in dt.Columns)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
                //写Excel
                FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();
            }
            catch (Exception ex)
            {
                string s = ex.Message;
            }
            finally
            {
                workbook = null;
            }
        }
        private void CopyRow(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            IRow destinationRow;
            var getRows = destinationWorksheet.GetRow(destinationRowNum);
            if (getRows == null)
            {
                destinationRow = destinationWorksheet.CreateRow(destinationRowNum);
            }
            else
            {
                destinationRow = getRows;
            }

            IRow sourceRow = sourceWorksheet.GetRow(sourceRowNum);

            // Loop through source columns to add to new row
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = destinationRow.CreateCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }

                // Copy style from old cell and apply to new cell
                ICellStyle newCellStyle = workbook.CreateCellStyle();
                newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
                newCell.CellStyle = newCellStyle;

                // If there is a cell comment, copy
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.Blank:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.Boolean:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.Error:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.Formula:
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.Numeric:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.String:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < sourceWorksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = sourceWorksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(destinationRow.RowNum,
                                                                                destinationRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    destinationWorksheet.AddMergedRegion(newCellRangeAddress);
                }
            }
        }
Example #24
0
        private void generate_my_data()
        {
            ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//*

            DataView  view  = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
            DataTable table = view.ToTable();

            DataTable dT = (DataTable)Session["header_01"];

            table.TableName = "Załatwienia";
            table.Columns.Remove("id_");
            table.Columns.Remove("id_tabeli");
            table.Columns.Remove("d_17");
            table.Columns.Remove("d_18");
            table.Columns.Remove("d_19");
            table.Columns.Remove("d_20");

            var  crs  = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0);
            IRow row0 = sheet0.CreateRow(0);

            #region tabela1

            foreach (DataRow dR in getData(dT, "Column1=3"))
            {
                string text = dR[1].ToString().Trim();
                int    exc1 = int.Parse(dR[4].ToString().Trim());
                int    exc2 = int.Parse(dR[5].ToString().Trim());
                int    exc3 = int.Parse(dR[6].ToString().Trim());
                int    exc4 = int.Parse(dR[7].ToString().Trim());
                row0.CreateCell(exc3).SetCellValue(text);
                if ((exc1 != exc2) || (exc3 != exc4))
                {
                    crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
                    sheet0.AddMergedRegion(crs);
                }
            }

            row0 = sheet0.CreateRow(1);
            foreach (DataRow dR in getData(dT, "Column1=2"))
            {
                string text = dR[1].ToString().Trim();
                int    exc1 = int.Parse(dR[4].ToString().Trim());
                int    exc2 = int.Parse(dR[5].ToString().Trim());
                int    exc3 = int.Parse(dR[6].ToString().Trim());
                int    exc4 = int.Parse(dR[7].ToString().Trim());
                row0.CreateCell(exc3).SetCellValue(text);
                if ((exc1 != exc2) || (exc3 != exc4))
                {
                    crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
                    sheet0.AddMergedRegion(crs);
                }
            }

            row0 = sheet0.CreateRow(2);
            foreach (DataRow dR in getData(dT, "Column1=1"))
            {
                string text = dR[1].ToString().Trim();
                int    exc1 = int.Parse(dR[4].ToString().Trim());
                int    exc2 = int.Parse(dR[5].ToString().Trim());
                int    exc3 = int.Parse(dR[6].ToString().Trim());
                int    exc4 = int.Parse(dR[7].ToString().Trim());
                row0.CreateCell(exc3).SetCellValue(text);
                if ((exc1 != exc2) || (exc3 != exc4))
                {
                    crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
                    sheet0.AddMergedRegion(crs);
                }
            }

            int rol = 3;
            foreach (DataRow rowik in table.Rows)
            {
                row0 = sheet0.CreateRow(rol);
                for (int i = 0; i < rowik.ItemArray.Length; i++)
                {
                    try
                    {
                        int        ji        = int.Parse(rowik[i].ToString().Trim());
                        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                        row0.CreateCell(i).SetCellValue(ji);
                        row0.Cells[i].CellStyle = cellStyle;
                    }
                    catch (Exception)
                    {
                        row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
                    }
                }
                rol++;
            }// end foreach

            #endregion tabela1

            #region drugi arkusz

            // druga tabela
            view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty);

            table = view.ToTable();

            table.TableName = "Załatwienia";
            table.Columns.Remove("ident");
            table.Columns.Remove("sesja");
            table.Columns.Remove("id_sedziego");
            table.Columns.Remove("id_tabeli");
            table.Columns.Remove("id_dzialu");
            table.Columns.Remove("d_12");
            table.Columns.Remove("d_13");
            table.Columns.Remove("d_14");
            table.Columns.Remove("d_15");
            table.Columns.Remove("d_16");
            table.Columns.Remove("d_17");
            table.Columns.Remove("d_18");
            table.Columns.Remove("d_19");
            table.Columns.Remove("d_20");
            table.Columns.Remove("d_21");
            table.Columns.Remove("d_22");
            //
            //robienie
            int ro = 2;

            //-----------------

            IDataFormat format = hssfworkbook.CreateDataFormat();

            ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
            IRow   row2   = sheet1.CreateRow(0);

            dT.Clear();
            dT = (DataTable)Session["header_02"];
            //===========

            foreach (DataRow dR in getData(dT, "Column1=3"))
            {
                string text = dR[1].ToString().Trim();
                int    exc1 = int.Parse(dR[4].ToString().Trim());
                int    exc2 = int.Parse(dR[5].ToString().Trim());
                int    exc3 = int.Parse(dR[6].ToString().Trim());
                int    exc4 = int.Parse(dR[7].ToString().Trim());
                row2.CreateCell(exc3).SetCellValue(text);
                if ((exc1 != exc2) || (exc3 != exc4))
                {
                    crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
                    sheet0.AddMergedRegion(crs);
                }
            }

            row2 = sheet0.CreateRow(1);
            foreach (DataRow dR in getData(dT, "Column1=2"))
            {
                string text = dR[1].ToString().Trim();
                int    exc1 = int.Parse(dR[4].ToString().Trim());
                int    exc2 = int.Parse(dR[5].ToString().Trim());
                int    exc3 = int.Parse(dR[6].ToString().Trim());
                int    exc4 = int.Parse(dR[7].ToString().Trim());
                row2.CreateCell(exc3).SetCellValue(text);
                if ((exc1 != exc2) || (exc3 != exc4))
                {
                    crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
                    sheet0.AddMergedRegion(crs);
                }
            }

            row2 = sheet0.CreateRow(2);
            foreach (DataRow dR in getData(dT, "Column1=1"))
            {
                string text = dR[1].ToString().Trim();
                int    exc1 = int.Parse(dR[4].ToString().Trim());
                int    exc2 = int.Parse(dR[5].ToString().Trim());
                int    exc3 = int.Parse(dR[6].ToString().Trim());
                int    exc4 = int.Parse(dR[7].ToString().Trim());
                row2.CreateCell(exc3).SetCellValue(text);
                if ((exc1 != exc2) || (exc3 != exc4))
                {
                    crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
                    sheet0.AddMergedRegion(crs);
                }
            }

            rol = 3;
            foreach (DataRow rowik in table.Rows)
            {
                row2 = sheet0.CreateRow(rol);
                for (int i = 0; i < rowik.ItemArray.Length; i++)
                {
                    try
                    {
                        int        ji        = int.Parse(rowik[i].ToString().Trim());
                        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                        row2.CreateCell(i).SetCellValue(ji);
                        row2.Cells[i].CellStyle = cellStyle;
                    }
                    catch (Exception)
                    {
                        row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
                    }
                }
                rol++;
            }// end foreach

            foreach (DataRow rowik in table.Rows)
            {
                row2 = sheet1.CreateRow(ro);
                for (int i = 0; i < rowik.ItemArray.Length; i++)
                {
                    try
                    {
                        int        ji        = int.Parse(rowik[i].ToString().Trim());
                        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                        row2.CreateCell(i).SetCellValue(ji);
                        row2.Cells[i].CellStyle = cellStyle;
                    }
                    catch (Exception)
                    {
                        row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
                    }
                }
                ro++;
            }// end foreach

            #endregion drugi arkusz

            // trzeci sheet

            view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);

            table = view.ToTable();

            table.Columns.Remove("ident");
            table.Columns.Remove("sesja");
            table.Columns.Remove("id_sedziego");
            table.Columns.Remove("id_tabeli");
            table.Columns.Remove("id_dzialu");
            table.Columns.Remove("d_10");
            table.Columns.Remove("d_11");
            table.Columns.Remove("d_12");
            table.Columns.Remove("d_13");
            table.Columns.Remove("d_14");
            table.Columns.Remove("d_15");
            table.Columns.Remove("d_16");
            table.Columns.Remove("d_17");
            table.Columns.Remove("d_18");
            table.Columns.Remove("d_19");
            table.Columns.Remove("d_20");
            table.Columns.Remove("d_21");
            table.Columns.Remove("d_22");

            sheet1.AutoSizeColumn(0, true);
            sheet1.AutoSizeColumn(1, true);

            ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");

            row2 = sheet2.CreateRow(0);
            row2.CreateCell(0).SetCellValue("L.p.");
            row2.CreateCell(1).SetCellValue("Nazwisko");
            row2.CreateCell(2).SetCellValue("Imię");
            row2.CreateCell(3).SetCellValue("Funkcja");
            row2.CreateCell(4).SetCellValue("Stanowisko");

            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
            sheet2.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
            sheet2.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
            sheet2.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
            sheet2.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
            sheet2.AddMergedRegion(crs);

            row2.CreateCell(5).SetCellValue("Wyznaczenia");
            crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
            sheet2.AddMergedRegion(crs);

            row2 = sheet2.CreateRow(1);

            row2.CreateCell(5).SetCellValue("GU bez ''of''");
            row2.CreateCell(6).SetCellValue("C-GC");
            row2.CreateCell(7).SetCellValue("GU ''of''");
            row2.CreateCell(8).SetCellValue("GU Razem");
            row2.CreateCell(9).SetCellValue("GUp bez  '''of'");
            row2.CreateCell(10).SetCellValue("GUp ''of''");
            row2.CreateCell(11).SetCellValue("WSC");
            row2.CreateCell(12).SetCellValue("Razem");
            row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
            ro = 2;

            foreach (DataRow rowik in table.Rows)
            {
                row2 = sheet2.CreateRow(ro);
                for (int i = 0; i < rowik.ItemArray.Length; i++)
                {
                    try
                    {
                        int        ji        = int.Parse(rowik[i].ToString().Trim());
                        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                        row2.CreateCell(i).SetCellValue(ji);
                        row2.Cells[i].CellStyle = cellStyle;
                    }
                    catch (Exception)
                    {
                        row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
                    }
                }
                ro++;
            }// end foreach

            // czwarty sheet

            view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);

            table = view.ToTable();

            table.Columns.Remove("ident");
            table.Columns.Remove("sesja");
            table.Columns.Remove("id_sedziego");
            table.Columns.Remove("id_tabeli");
            //table.Columns.Remove("id_dzialu");
            table.Columns.Remove("d_09");
            table.Columns.Remove("d_10");
            table.Columns.Remove("d_11");
            table.Columns.Remove("d_12");
            table.Columns.Remove("d_13");
            table.Columns.Remove("d_14");
            table.Columns.Remove("d_15");
            table.Columns.Remove("d_16");
            table.Columns.Remove("d_17");
            table.Columns.Remove("d_18");
            table.Columns.Remove("d_19");
            table.Columns.Remove("d_20");
            table.Columns.Remove("d_21");
            table.Columns.Remove("d_22");

            ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");

            row2 = sheet3.CreateRow(0);
            row2.CreateCell(0).SetCellValue("L.p.");
            row2.CreateCell(1).SetCellValue("Nazwisko");
            row2.CreateCell(2).SetCellValue("Imię");
            row2.CreateCell(3).SetCellValue("Funkcja");
            row2.CreateCell(4).SetCellValue("Stanowisko");

            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
            sheet3.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
            sheet3.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
            sheet3.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
            sheet3.AddMergedRegion(crs);
            crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
            sheet3.AddMergedRegion(crs);

            row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
            crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
            sheet3.AddMergedRegion(crs);

            row2 = sheet3.CreateRow(1);

            row2.CreateCell(5).SetCellValue("GU bez ''of''");
            row2.CreateCell(6).SetCellValue("C-GC");
            row2.CreateCell(7).SetCellValue("GU ''of''");
            row2.CreateCell(8).SetCellValue("GU Razem");
            row2.CreateCell(9).SetCellValue("GUp bez  '''of'");
            row2.CreateCell(10).SetCellValue("GUp ''of''");
            row2.CreateCell(11).SetCellValue("WSC");
            row2.CreateCell(12).SetCellValue("Razem");
            // row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
            ro = 2;

            foreach (DataRow rowik in table.Rows)
            {
                row2 = sheet3.CreateRow(ro);
                for (int i = 0; i < rowik.ItemArray.Length; i++)
                {
                    try
                    {
                        int        ji        = int.Parse(rowik[i].ToString().Trim());
                        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                        row2.CreateCell(i).SetCellValue(ji);
                        row2.Cells[i].CellStyle = cellStyle;
                    }
                    catch (Exception)
                    {
                        row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
                    }
                }
                ro++;
            }// end foreach
        }
Example #25
0
        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="dt">源数据</param>
        /// <param name="fields">字段名(与 names 列名顺序一致,含个数)</param>
        /// <param name="names">列名(与 fields 字段名顺序一致,含个数)</param>
        /// <param name="merger">合并列相同的值,用于上下行单元格重复合并(多个列间用半角逗号间隔)</param>
        /// <param name="totalColumn">合计列的值,仅对数字、货币类型有效(在最后一行自动合计显示)</param>
        /// <returns>返回生成后的URL下载地址</returns>
        public string ToExcel(string fileName, DataTable dt, string[] fields, string[] names, int[] merger, int[] totalColumn = null, Dictionary <int, int> columnWidth = null)
        {
            if (dt != null && dt.Rows.Count > 0 && fields.Length == names.Length)
            {
                //创建Excel文件
                string sheetName  = fileName;
                string headerText = fileName;

                fileName = string.Format("{0}_{1}.xls", fileName, DateTime.Now.ToString("yyyy-MM-dd"));
                HSSFWorkbook book = new HSSFWorkbook();
                book.DocumentSummaryInformation = DSI;
                book.SummaryInformation         = SummaryInfo(fileName);

                //创建表
                ISheet iSheet = book.CreateSheet(sheetName);

                //创建数据格式
                IDataFormat iDataFormat = book.CreateDataFormat();
                //设置日期格式
                ICellStyle dataStyle = book.CreateCellStyle();

                int namesCount  = names.Length;
                int fieldsCount = fields.Length;


                #region //自适应列宽

                int   colCount = names.Length;
                int[] colWidth = new int[colCount];
                for (int i = 0; i < namesCount; i++)
                {
                    //colWidth[i] = Encoding.GetEncoding(936).GetBytes(names[i]).Length;
                    //colWidth[i] = (colWidth[i] < 5) ? 5 : colWidth[i] + 2;
                    if (columnWidth != null && columnWidth.ContainsKey(i))
                    {
                        colWidth[i] = (columnWidth[i] < 5) ? 5 : columnWidth[i] + 2;
                    }
                    else
                    {
                        colWidth[i] = Encoding.GetEncoding(936).GetBytes(names[i]).Length;
                        colWidth[i] = (colWidth[i] < 5) ? 5 : colWidth[i] + 2;
                    }
                }

                DataRowCollection drc = dt.Rows;
                int rowCount          = drc.Count;
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        int tmp = Encoding.GetEncoding(936).GetBytes(drc[i][fields[j]].ToString()).Length;
                        if (tmp > colWidth[j])
                        {
                            colWidth[j] = tmp;
                        }
                    }
                }

                #endregion

                IFont      titleFont = book.CreateFont();
                IFont      headFont  = book.CreateFont();
                IFont      textFont  = book.CreateFont();
                ICellStyle css       = book.CreateCellStyle();

                //
                int     cur = 0;
                int     tab = 1;
                decimal dec = 0;
                Dictionary <int, decimal> heji = new Dictionary <int, decimal>();

                for (int index = 0; index < rowCount; index++)
                {
                    #region 设置表头、列名、样式

                    if (index == 0 || index % this.MaxRows == 0)
                    {
                        if (cur != 0)
                        {
                            iSheet = book.CreateSheet(string.Format("{0}({1})", sheetName, tab));
                            tab++;
                        }

                        //表头
                        IRow header = iSheet.CreateRow(0);
                        header.HeightInPoints = 25;
                        //样式
                        ICellStyle cellStyle = book.CreateCellStyle();
                        cellStyle.Alignment         = HorizontalAlignment.CENTER;
                        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

                        cellStyle.BorderTop    = BorderStyle.THIN;
                        cellStyle.BorderBottom = BorderStyle.THIN;
                        cellStyle.BorderLeft   = BorderStyle.THIN;
                        cellStyle.BorderRight  = BorderStyle.THIN;

                        titleFont.FontHeightInPoints = 18;
                        cellStyle.SetFont(titleFont);

                        header.CreateCell(0).SetCellValue(headerText);
                        header.GetCell(0).CellStyle = cellStyle;

                        CellRangeAddress ra = new CellRangeAddress(0, 0, 0, colCount - 1);
                        iSheet.AddMergedRegion(ra);
                        ((HSSFSheet)iSheet).SetEnclosedBorderOfRegion(ra, BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

                        //
                        //列头
                        header = iSheet.CreateRow(1);
                        header.HeightInPoints       = 18;
                        cellStyle                   = book.CreateCellStyle();
                        cellStyle.Alignment         = HorizontalAlignment.CENTER;
                        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

                        cellStyle.BorderTop    = BorderStyle.THIN;
                        cellStyle.BorderBottom = BorderStyle.THIN;
                        cellStyle.BorderLeft   = BorderStyle.THIN;
                        cellStyle.BorderRight  = BorderStyle.THIN;

                        headFont.FontHeightInPoints = 12;
                        cellStyle.SetFont(headFont);

                        for (int i = 0; i < namesCount; i++)
                        {
                            header.CreateCell(i).SetCellValue(names[i]);
                            header.GetCell(i).CellStyle = cellStyle;
                            iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 1) * 256)));
                        }

                        cur = 2;
                    }

                    #endregion

                    #region 填充内容

                    IRow rows = iSheet.CreateRow(cur);
                    rows.HeightInPoints = 18;

                    for (int i = 0; i < fieldsCount; i++)
                    {
                        ICell cell = rows.CreateCell(i);
                        css.Alignment         = HorizontalAlignment.LEFT;
                        css.VerticalAlignment = VerticalAlignment.CENTER;
                        css.WrapText          = true;

                        css.BorderTop    = BorderStyle.THIN;
                        css.BorderBottom = BorderStyle.THIN;
                        css.BorderLeft   = BorderStyle.THIN;
                        css.BorderRight  = BorderStyle.THIN;

                        textFont.FontHeightInPoints = 10;
                        css.SetFont(textFont);
                        cell.CellStyle = css;

                        if (index > 0 && IsExistMerger(merger, i) && (index % this.MaxRows != 0))
                        {
                            if (string.Compare(Convert.ToString(drc[index][fields[i]]), Convert.ToString(drc[index - 1][fields[i]])) == 0 &&
                                string.Compare(Convert.ToString(drc[index][fields[merger[0]]]), Convert.ToString(drc[index - 1][fields[merger[0]]])) == 0)
                            {
                                iSheet.AddMergedRegion(new CellRangeAddress(cur - 1, cur, i, i));
                            }
                        }

                        string val = Convert.ToString(drc[index][fields[i]]);
                        dec = 0;
                        this.SetCellValue(cell, iDataFormat, dataStyle, drc[index][fields[i]].GetType().ToString(), val, out dec);
                        iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 1) * 256)));

                        if (IsExistMerger(totalColumn, i))
                        {
                            if (!heji.ContainsKey(i))
                            {
                                heji.Add(i, dec);
                            }
                            else
                            {
                                dec += heji[i];
                                heji.Remove(i);
                                heji.Add(i, dec);
                            }
                        }
                    }

                    #endregion

                    cur++;
                }

                #region 对指定列进行合计

                int beginColumn = 0;
                if (totalColumn != null && totalColumn.Length > 0)
                {
                    IRow rows = iSheet.CreateRow(cur);
                    rows.HeightInPoints = 18;

                    for (int i = 0; i < fieldsCount; i++)
                    {
                        ICell cell = rows.CreateCell(i);
                        css.Alignment         = HorizontalAlignment.LEFT;
                        css.VerticalAlignment = VerticalAlignment.CENTER;
                        css.WrapText          = true;

                        css.BorderTop    = BorderStyle.THIN;
                        css.BorderBottom = BorderStyle.THIN;
                        css.BorderLeft   = BorderStyle.THIN;
                        css.BorderRight  = BorderStyle.THIN;

                        textFont.FontHeightInPoints = 10;
                        css.SetFont(textFont);
                        cell.CellStyle = css;

                        dec = 0;
                        if (IsExistMerger(totalColumn, i))
                        {
                            this.SetCellValue(cell, iDataFormat, dataStyle, heji[i].GetType().ToString(), heji[i].ToString(), out dec);
                            if (beginColumn == 0)
                            {
                                beginColumn = i;
                            }
                        }
                        else if (beginColumn > 0)
                        {
                            cell = rows.GetCell(0);

                            css.Alignment = HorizontalAlignment.RIGHT;
                            css.SetFont(textFont);
                            cell.CellStyle = css;

                            iSheet.AddMergedRegion(new CellRangeAddress(cur, cur, 0, beginColumn - 1));
                            this.SetCellValue(cell, iDataFormat, dataStyle, "System.String", "合计:", out dec);
                            beginColumn = -1;
                        }
                        else
                        {
                            this.SetCellValue(cell, iDataFormat, dataStyle, "System.String", "", out dec);
                        }

                        iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 2) * 256)));
                    }
                }

                #endregion

                //输出文件
                return(this.OutputFile(book, fileName));
            }
            return(string.Empty);
        }
Example #26
0
        /// <summary>
        /// 操作已经生成的Excel
        /// </summary>
        /// <param name="fullPath"></param>
        /// <param name="ivm"></param>
        /// <returns></returns>
        public static bool ExcelDraw(string fullPath, QueryDataInputVM ivm)
        {
            //需要绘制的记录
            var needDraw = "DatabaseTableDesign,syslog".ToLower().Split(',');

            if (!needDraw.Contains(ivm.tableName?.ToLower()))
            {
                return(true);
            }

            string strExtName = Path.GetExtension(fullPath);

            IWorkbook workbook = null;

            using (FileStream file = new FileStream(fullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (strExtName.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(file);
                }
                if (strExtName.Equals(".xlsx"))
                {
                    workbook = new XSSFWorkbook(file);
                }
            }

            ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);

            switch (ivm.tableName?.ToLower())
            {
            //数据库表设计
            case "databasetabledesign":
            {
                //冻结首行首列
                sheet.CreateFreezePane(0, 1);

                var rows = sheet.GetRowEnumerator();

                var styleH = CreateCellStyle(workbook, StyleType.headLeft);

                while (rows.MoveNext())
                {
                    IRow row;
                    if (fullPath.Contains(".xlsx"))
                    {
                        row = (XSSFRow)rows.Current;
                    }
                    else
                    {
                        row = (HSSFRow)rows.Current;
                    }

                    var cc = row.GetCell(1);
                    if (string.IsNullOrWhiteSpace(cc.StringCellValue))
                    {
                        foreach (var cell in row.Cells)
                        {
                            cell.CellStyle = styleH;
                        }

                        //合并
                        sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, row.Cells.Count - 1));
                    }
                }
            }
            break;

            default:
                break;
            }

            using (FileStream file = new FileStream(fullPath, FileMode.OpenOrCreate))
            {
                workbook.Write(file);
                workbook.Close();
            }
            return(false);
        }
Example #27
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            #region 右击文件 属性信息
            {
                //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                //dsi.Company = "";
                //workbook.DocumentSummaryInformation = dsi;

                //SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //si.Author = ""; //填加xls文件作者信息
                //si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
                //si.LastAuthor = ""; //填加xls文件最后保存者信息
                //si.Comments = "说明信息"; //填加xls文件作者信息
                //si.Title = "NPOI测试"; //填加xls文件标题信息
                //si.Subject = "NPOI测试Demo";//填加文件主题信息
                //si.CreateDateTime = DateTime.Now;
                //workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    //长度大于255,后面省略
                    if (intTemp > 254)
                    {
                        int lh = dtSource.Rows[i][j].ToString().Length;
                        dtSource.Rows[i][j] = dtSource.Rows[i][j].ToString().Substring(0, lh - 4) + "…";
                        intTemp             = 254;
                    }
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    bool hasHeader = false;
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    if (!string.IsNullOrWhiteSpace(strHeaderText))
                    {
                        hasHeader = true;
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();
                    }
                    #endregion


                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(hasHeader?1:0);


                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);


                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = hasHeader?2:1;
                }
                #endregion


                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                    case "System.String":    //字符串类型
                        newCell.SetCellValue(drValue);
                        break;

                    case "System.DateTime":    //日期类型
                        //DateTime dateV;
                        //DateTime.TryParse(drValue, out dateV);
                        //newCell.SetCellValue(dateV);
                        //newCell.CellStyle = dateStyle;//格式化显示
                        newCell.SetCellValue(drValue);
                        break;

                    case "System.Boolean":    //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case "System.Int16":    //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        long intV = 0;
                        long.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case "System.Decimal":    //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case "System.DBNull":    //空值处理
                        newCell.SetCellValue("");
                        break;

                    default:
                        newCell.SetCellValue("");
                        break;
                    }
                }
                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                return(ms);
            }
        }
Example #28
0
        //row 0-4
        protected static int SetBorangAHeader(IWorkbook workbook, ISheet sheet1, int bulan, int tahun)
        {
            var associativeArray = new Dictionary <int?, string>()
            {
                { 1, "JANUARI" }, { 2, "FEBRUARI" }, { 3, "MAC" }, { 4, "APRIL" }, { 5, "MEI" }, { 6, "JUN" }, { 7, "JULAI" }, { 8, "OGOS" }, { 9, "SEPTEMBER" }, { 10, "OKTOBER" }, { 11, "NOVEMBER" }, { 12, "DISEMBER" }
            };
            string bulanString = "";

            foreach (var m in associativeArray)
            {
                if (bulan == m.Key)
                {
                    bulanString = m.Value;
                }
            }

            string   bulanTahunString = string.Format("{0} {1}", bulanString, tahun);
            DateTime tarikhAkhir      = new DateTime(tahun, bulan, 1);

            tarikhAkhir = tarikhAkhir.AddMonths(1);
            tarikhAkhir = tarikhAkhir.AddDays(-1);

            ICellStyle alignCenter = AlignCenter(workbook);
            int        colWidth    = 6;

            for (int row = 0; row <= 4; row++)
            {
                IRow  nRow  = sheet1.CreateRow(row);
                ICell nCell = nRow.CreateCell(0);
                nCell.CellStyle = alignCenter;
                switch (row)
                {
                case (0):
                    nCell.SetCellValue("PERTUBUHAN KESELEMATAN SOSIAL");
                    ICell cellBorang = nRow.CreateCell(colWidth);
                    cellBorang.SetCellValue("BORANG");
                    break;

                case (1):
                    nCell.SetCellValue("JADUAL CARUMAN BULANAN");
                    ICell cell8a = nRow.CreateCell(colWidth);
                    cell8a.SetCellValue("8A");
                    break;

                case (2):
                    nCell.SetCellValue("UNTUK CARUMAN BULAN " + bulanTahunString);
                    break;

                case (3):
                    nCell.SetCellValue("JUMLAH CARUMAN UNTUK BULAN DI ATAS HENDAKLAH DIBAYAR");
                    break;

                case (4):
                    nCell.SetCellValue("TIDAK LEWAT DARIPADA " + string.Format("{0:dd/MM/yyyy}", tarikhAkhir));
                    ICell cellLembaran = nRow.CreateCell(colWidth);
                    cellLembaran.SetCellValue("LEMBARAN: 1");
                    break;

                default:
                    break;
                }
                //merge all column
                var cra0 = new CellRangeAddress(row, row, 0, colWidth - 1);
                sheet1.AddMergedRegion(cra0);
            }
            return(4);
        }
Example #29
0
        public override void OnRenderMain(FlatProject project, ParsedLog log)
        {
            int rowPos = LG_ROW_POS_CONTENT_START;

            foreach (ParsedLogLine line in log.LogLineList)
            {
                int colPos = LG_COL_POS_CONTENT_START;

                if (project.ShowFileName)
                {
                    ICell fileCell = GetLogContentStringCell(colPos, rowPos);
                    fileCell.SetCellValue(line.File.Name);
                    SetCellWidthToDictionary(LG_COL_NAME_FILE, line.File.Name);
                    colPos++;
                }

                ICell lineNumCell = GetLogContentCenterCell(colPos, rowPos);
                lineNumCell.SetCellValue(line.LineNumber);
                SetCellWidthToDictionary(LG_COL_NAME_LINE, line.LineNumber.ToString());
                colPos++;

                if (line.HasError)
                {
                    ICell notParsedCell = GetLogContentStringCell(colPos, rowPos);
                    notParsedCell.SetCellValue(line.NotParsedLog);

                    if (line.ColumnList.Count > 1)
                    {
                        int cellRange = colPos;
                        foreach (ParsedLogColumn col in line.ColumnList)
                        {
                            if (col.ColumnDefinition.Visble)
                            {
                                cellRange++;
                            }
                        }

                        CellRangeAddress region = new CellRangeAddress(rowPos, rowPos, colPos, cellRange - 1);
                        logSheet.AddMergedRegion(region);

                        RegionUtil.SetBorderTop(BORDER_THIN, region, logSheet, workbook);
                        RegionUtil.SetBorderBottom(BORDER_THIN, region, logSheet, workbook);
                        RegionUtil.SetBorderRight(BORDER_THIN, region, logSheet, workbook);
                        RegionUtil.SetBorderLeft(BORDER_THIN, region, logSheet, workbook);
                    }
                }
                else
                {
                    foreach (ParsedLogColumn col in line.ColumnList)
                    {
                        if (!col.ColumnDefinition.Visble)
                        {
                            continue;
                        }

                        ICell cell = null;

                        if (col.ColumnDefinition.IsDateTimeField)
                        {
                            DateTime time = new DateTime(long.Parse(col.Value));

                            cell = GetLogContentDateCell(colPos, rowPos);
                            cell.SetCellValue(time);
                            SetCellWidthToDictionary(col.ColumnDefinition.ColumnName, time.ToString(FORMAT_STR_DATE));
                        }
                        else
                        {
                            cell = GetLogContentStringCell(colPos, rowPos);
                            cell.SetCellValue(col.Value);
                            SetCellWidthToDictionary(col.ColumnDefinition.ColumnName, col.Value);
                        }

                        colPos++;
                    }
                }

                rowPos++;
            }

            ResizeLogColumns(project, project.PatternDefinition.ColumnDefinitionList);
        }
Example #30
0
 //合并单元格
 public static void MergedCell(ISheet curSheet, int BeginRowIndex, int EndRowIndex, int BeginColumnIndex, int EndColumnIndex)
 {
     curSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(BeginRowIndex, EndRowIndex, BeginColumnIndex, EndColumnIndex));
     //CellRangeAddress()该方法的参数次序是:开始行号,结束行号,开始列号,结束列号。
 }
Example #31
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            var    workbook = new HSSFWorkbook();
            ISheet sheet    = workbook.CreateSheet("sheet1");

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = "文件作者信息";  //填加xls文件作者信息
                si.ApplicationName          = "创建程序信息";  //填加xls文件创建程序信息
                si.LastAuthor               = "最后保存者信息"; //填加xls文件最后保存者信息
                si.Comments                 = "作者信息";    //填加xls文件作者信息
                si.Title                    = "标题信息";    //填加xls文件标题信息
                si.Subject                  = "主题信息";    //填加文件主题信息
                si.CreateDateTime           = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        var headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        var headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        var font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        var headerRow = sheet.CreateRow(1);
                        var headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        var font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);
                        var maxCellWidth = 255 * 100;
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //设置列宽
                            var colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
                            if (colWidth > maxCellWidth)
                            {
                                colWidth = maxCellWidth;
                            }
                            sheet.SetColumnWidth(column.Ordinal, colWidth);
                        }
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion


                #region 填充内容
                var dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    var newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                    case "System.String":    //字符串类型
                        newCell.SetCellValue(drValue);
                        break;

                    case "System.DateTime":    //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle;    //格式化显示
                        break;

                    case "System.Boolean":    //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case "System.Int16":    //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case "System.Decimal":    //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case "System.DBNull":    //空值处理
                        newCell.SetCellValue("");
                        break;

                    default:
                        newCell.SetCellValue("");
                        break;
                    }
                }
                #endregion

                rowIndex++;
            }
            MemoryStream ms = new MemoryStream();
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }
Example #32
0
        private void ExportDataItem(DataTable dt)
        {
            DataRow dr       = dt.Rows[0];
            string  filename = "培训实施记录--" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
            HttpContext.Current.Response.Clear();
            //1.读取Excel到FileStream
            using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("培训实施记录.xls")))
            {
                IWorkbook wk     = new HSSFWorkbook(fs);
                ISheet    sheet0 = wk.GetSheetAt(0);
                for (int i = 0, length = dt.Rows.Count; i < length; i++)
                {
                    IRow row = sheet0.CreateRow(i + 2);
                    row.HeightInPoints = 20;
                    row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
                    row.CreateCell(1).SetCellValue(dt.Rows[i]["PX_BM"].ToString());
                    row.CreateCell(2).SetCellValue(dt.Rows[i]["PX_BH"].ToString());
                    row.CreateCell(3).SetCellValue(dt.Rows[i]["SPLX"].ToString() == "NDPXJH" ? "年度" : "临时");
                    row.CreateCell(4).SetCellValue(dt.Rows[i]["PX_FS"].ToString() == "n" ? "内部" : "外部");
                    row.CreateCell(5).SetCellValue(dt.Rows[i]["PX_XMMC"].ToString());
                    row.CreateCell(6).SetCellValue(dt.Rows[i]["PX_SJ"].ToString() == "1" ? "第一季度" : dt.Rows[i]["PX_SJ"].ToString() == "2" ? "第二季度" : dt.Rows[i]["PX_SJ"].ToString() == "3" ? "第三季度" : "第四季度");
                    row.CreateCell(7).SetCellValue(dt.Rows[i]["PX_DD"].ToString());
                    row.CreateCell(8).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString());
                    row.CreateCell(9).SetCellValue(dt.Rows[i]["PX_DX"].ToString());
                    row.CreateCell(10).SetCellValue(dt.Rows[i]["PX_RS"].ToString());
                    row.CreateCell(11).SetCellValue(dt.Rows[i]["PX_XS"].ToString());
                    row.CreateCell(12).SetCellValue(dt.Rows[i]["PX_FYYS"].ToString());
                    row.CreateCell(13).SetCellValue(dt.Rows[i]["PX_SJSJ"].ToString());
                    row.CreateCell(14).SetCellValue(dt.Rows[i]["PX_SJDD"].ToString());
                    row.CreateCell(15).SetCellValue(dt.Rows[i]["PX_SJRY"].ToString());
                    row.CreateCell(16).SetCellValue(dt.Rows[i]["PX_SJRS"].ToString());
                    row.CreateCell(17).SetCellValue(dt.Rows[i]["PX_SJXS"].ToString());
                    row.CreateCell(18).SetCellValue(dt.Rows[i]["PX_SJBZ"].ToString());
                    NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
                    font1.FontName           = "仿宋"; //字体
                    font1.FontHeightInPoints = 9;    //字号
                    ICellStyle cells = wk.CreateCellStyle();
                    cells.SetFont(font1);
                    cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
                    cells.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;
                    cells.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;
                    cells.BorderTop    = NPOI.SS.UserModel.BorderStyle.THIN;
                    for (int j = 0; j <= 18; j++)
                    {
                        row.Cells[j].CellStyle = cells;
                    }
                }

                double num = 0;
                for (int i = 0, length = dt.Rows.Count; i < length; i++)
                {
                    num += CommonFun.ComTryDouble(dt.Rows[i]["PX_SJXS"].ToString());
                }

                IRow rowhz = sheet0.CreateRow(dt.Rows.Count + 2);
                for (int i = 0; i <= 18; i++)
                {
                    rowhz.CreateCell(i);
                }
                rowhz.GetCell(0).SetCellValue("人数合计");
                rowhz.GetCell(17).SetCellValue(num);
                rowhz.HeightInPoints = 20;
                NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
                font2.FontName           = "仿宋"; //字体
                font2.FontHeightInPoints = 9;    //字号
                ICellStyle cells2 = wk.CreateCellStyle();
                cells2.SetFont(font2);
                cells2.BorderBottom      = NPOI.SS.UserModel.BorderStyle.THIN;
                cells2.BorderLeft        = NPOI.SS.UserModel.BorderStyle.THIN;
                cells2.BorderRight       = NPOI.SS.UserModel.BorderStyle.THIN;
                cells2.BorderTop         = NPOI.SS.UserModel.BorderStyle.THIN;
                cells2.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                cells2.VerticalAlignment = VerticalAlignment.CENTER;
                for (int i = 0; i <= 18; i++)
                {
                    rowhz.Cells[i].CellStyle = cells2;
                }
                CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 2, dt.Rows.Count + 2, 0, 16);
                sheet0.AddMergedRegion(range1);
                //CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17);
                //sheet0.AddMergedRegion(range2);

                //IRow rowqz1 = sheet0.CreateRow(dt.Rows.Count + 6);
                //for (int i = 0; i <= 17; i++)
                //{
                //    rowqz1.CreateCell(i);
                //}
                //rowqz1.GetCell(0).SetCellValue("编制负责人:");
                //rowqz1.GetCell(2).SetCellValue(dr["ZDR"].ToString());
                //rowqz1.GetCell(5).SetCellValue("申报单位/部门主管:");
                //rowqz1.GetCell(7).SetCellValue(dr["SPR1"].ToString());
                //rowqz1.GetCell(8).SetCellValue(dr["SPR1_JL"].ToString() == "y" ? "同意" : dr["SPR1_JL"].ToString() == "n" ? "不同意" : "");
                //rowqz1.GetCell(12).SetCellValue("集团公司主管领导:");
                //rowqz1.GetCell(14).SetCellValue(dr["SPR2"].ToString());
                //rowqz1.GetCell(15).SetCellValue(dr["SPR2_JL"].ToString() == "y" ? "同意" : dr["SPR2_JL"].ToString() == "n" ? "不同意" : "");
                //for (int i = 0; i <= 17; i++)
                //{
                //    rowqz1.Cells[i].CellStyle = cells2;
                //}
                //CellRangeAddress range3 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 0, 1);
                //sheet0.AddMergedRegion(range3);
                ////CellRangeAddress range4 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 3, 4);
                ////sheet0.AddMergedRegion(range4);
                //CellRangeAddress range5 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 5, 6);
                //sheet0.AddMergedRegion(range5);
                ////CellRangeAddress range6 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 9, 11);
                ////sheet0.AddMergedRegion(range6);
                //CellRangeAddress range7 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 12, 13);
                //sheet0.AddMergedRegion(range7);

                //IRow rowqz2 = sheet0.CreateRow(dt.Rows.Count + 7);
                //for (int i = 0; i <= 17; i++)
                //{
                //    rowqz2.CreateCell(i);
                //}
                //rowqz2.GetCell(1).SetCellValue("日期:");
                //rowqz2.GetCell(2).SetCellValue(dr["ZDR_SJ"].ToString());
                //rowqz2.GetCell(6).SetCellValue("日期:");
                //rowqz2.GetCell(7).SetCellValue(dr["SPR1_SJ"].ToString());
                //rowqz2.GetCell(13).SetCellValue("日期:");
                //rowqz2.GetCell(14).SetCellValue(dr["SPR2_SJ"].ToString());
                //for (int i = 0; i <= 17; i++)
                //{
                //    rowqz2.Cells[i].CellStyle = cells2;
                //}

                MemoryStream file = new MemoryStream();
                wk.Write(file);
                HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
                HttpContext.Current.Response.End();
            }
        }
Example #33
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return(new MemoryStream());
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "http://....../";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                if (HttpContext.Current.Session["realname"] != null)
                {
                    si.Author = HttpContext.Current.Session["realname"].ToString();
                }
                else
                {
                    if (HttpContext.Current.Session["username"] != null)
                    {
                        si.Author = HttpContext.Current.Session["username"].ToString();
                    }
                }                                            //填加xls文件作者信息
                si.ApplicationName          = "NPOI";        //填加xls文件创建程序信息
                si.LastAuthor               = "OA系统";        //填加xls文件最后保存者信息
                si.Comments                 = "OA系统自动创建文件";  //填加xls文件作者信息
                si.Title                    = strHeaderText; //填加xls文件标题信息
                si.Subject                  = strHeaderText; //填加文件主题信息
                si.CreateDateTime           = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }

            /*
             * foreach (DataColumn item in dtSource.Columns)
             * {
             *  arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
             * }
             * */

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }

                /*
                 * for (int j = 0; j < dtSource.Columns.Count; j++)
                 * {
                 *  int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                 *  if (intTemp > arrColWidth[j])
                 *  {
                 *      arrColWidth[j] = intTemp;
                 *  }
                 * }
                 * */
            }
            #endregion
            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }

                        /*
                         * foreach (DataColumn column in dtSource.Columns)
                         * {
                         *  headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                         *  headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                         *
                         *  //设置列宽
                         *  sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                         * }
                         * */
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion


                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                    case "System.String":    //字符串类型
                        newCell.SetCellValue(drValue);
                        break;

                    case "System.DateTime":    //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle;    //格式化显示
                        break;

                    case "System.Boolean":    //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case "System.Int16":    //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case "System.Decimal":    //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case "System.DBNull":    //空值处理
                        newCell.SetCellValue("");
                        break;

                    default:
                        newCell.SetCellValue("");
                        break;
                    }
                }
                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                sheet    = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                return(ms);
            }
        }
Example #34
0
        public void excel()
        {
            IWorkbook workbook = new HSSFWorkbook();                 //döküman
            ISheet    sheet1   = workbook.CreateSheet("product 12"); //sayfa

            List <V_sold_product> pm = db.sold_product_Read().ToList();

            var style = workbook.CreateCellStyle();                   //sitil dosyası

            style.FillForegroundColor = HSSFColor.Blue.Index2;        //renk
            style.Alignment           = HorizontalAlignment.Center;   // metin hizalama
            style.VerticalAlignment   = VerticalAlignment.Center;     //metin hzalama
            style.FillPattern         = FillPattern.LessDots;         // renk doldurma

            sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 8)); //birleştirme
            var rowIndex = 0;
            var row      = sheet1.CreateRow(rowIndex).CreateCell(0);

            row.CellStyle = style;
            row.SetCellValue("deneme son vol:3");

            rowIndex += 2;

            var row2 = sheet1.CreateRow(rowIndex);

            row2.CreateCell(0).SetCellValue("SIRA NO"); //hücre değerleri
            sheet1.AutoSizeColumn(0);                   // hücre veriye göre boyutlandırma
            row2.CreateCell(1).SetCellValue("ARAC");
            sheet1.AutoSizeColumn(1);
            row2.CreateCell(2).SetCellValue("DOKUMAN NO");
            sheet1.AutoSizeColumn(8);
            row2.CreateCell(3).SetCellValue("BIRIM");
            sheet1.AutoSizeColumn(2);
            row2.CreateCell(4).SetCellValue("ODEME PLANI");
            sheet1.AutoSizeColumn(3);
            row2.CreateCell(5).SetCellValue("SATILAN URUN");
            sheet1.AutoSizeColumn(4);
            row2.CreateCell(6).SetCellValue("MALIYET");
            sheet1.AutoSizeColumn(5);
            row2.CreateCell(7).SetCellValue("TOPLAM");
            sheet1.AutoSizeColumn(6);
            row2.CreateCell(8).SetCellValue("EKLEME TARIHI");
            sheet1.AutoSizeColumn(7);
            rowIndex++;

            foreach (var item in pm)
            {
                var row1 = sheet1.CreateRow(rowIndex);
                row1.CreateCell(0).SetCellValue(item.SIRA_NO); //hücre değerleri
                sheet1.AutoSizeColumn(0);                      // hücre veriye göre boyutlandırma
                row1.CreateCell(1).SetCellValue(item.ARAC);
                sheet1.AutoSizeColumn(1);
                row1.CreateCell(2).SetCellValue(item.DOKUMAN_NO);
                sheet1.AutoSizeColumn(8);
                row1.CreateCell(3).SetCellValue(item.BIRIM);
                sheet1.AutoSizeColumn(2);
                row1.CreateCell(4).SetCellValue(item.ODEME_PLANI);
                sheet1.AutoSizeColumn(3);
                row1.CreateCell(5).SetCellValue(item.SATILAN_URUN.ToString());
                sheet1.AutoSizeColumn(4);
                row1.CreateCell(6).SetCellValue(item.MALIYET.ToString());
                sheet1.AutoSizeColumn(5);
                row1.CreateCell(7).SetCellValue(item.TOPLAM.ToString());
                sheet1.AutoSizeColumn(6);
                row1.CreateCell(8).SetCellValue(item.EKLEME_TARIHI.Value);
                sheet1.AutoSizeColumn(7);
                rowIndex++;
            }
            MemoryStream stream = new MemoryStream();

            workbook.Write(stream);


            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; producttable.xls");
            Response.AddHeader("content-length", stream.Length.ToString());
            Response.BinaryWrite(stream.ToArray());
            Response.End();
        }
Example #35
0
        public HSSFWorkbook DataTableToExcel(DataTable dt, string sheetName, string headerText)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet       sheet        = hssfworkbook.CreateSheet(sheetName);

            #region 设置文件属性信息
            ArrayList tagRed = new ArrayList();

            //创建一个摘要信息实体。
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Title          = headerText;
            si.Subject        = headerText;
            si.CreateDateTime = DateTime.Now;
            hssfworkbook.SummaryInformation = si;


            ICellStyle  dateStyle = hssfworkbook.CreateCellStyle();
            IDataFormat format    = hssfworkbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            #endregion


            #region 获取列名
            List <string> columnNameArry = new List <string>();
            DataTable     dt_TemptColumn = BLL.TField.Instance.GetTemptColumnNameByTableName(ttCode);
            int           tempt_Count    = dt_TemptColumn.Columns.Count;

            int excelColumn_Count = 0;
            for (int k = 0; k < tempt_Count; k++)
            {
                string   column_Name = dt_TemptColumn.Columns[k].ToString();
                string[] columns     = column_Name.Split('_');

                System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"Tempf.*");
                bool m = reg.IsMatch(column_Name);

                if (m)
                {
                    string tfDesName = string.Empty;//字段名

                    //需要特殊处理的列
                    if (columns.Length >= 2)
                    {
                        //如果是 推荐活动字段列 则不生成EXCEL模板列
                        if (columns[1] == "Activity")
                        {
                            BLL.Loger.Log4Net.Info("DataTable正在生成,推荐活动字段不生成列名!");
                            continue;
                        }

                        string addDes = string.Empty;

                        //如果为Province且有columns有三个,则表示是省份名;
                        if (columns[1] == "Province" && columns.Length == 3)
                        {
                            addDes = "(省)";
                        }
                        //如果为City且有columns有三个,则表示是城市名;
                        else if (columns[1] == "City" && columns.Length == 3)
                        {
                            addDes = "(市)";
                        }
                        //如果为Country且有columns有三个,则表示是县名;
                        else if (columns[1] == "Country" && columns.Length == 3)
                        {
                            addDes = "(县)";
                        }
                        //如果为startdata或starttime,则表示是开始日期/时间段;
                        else if (columns[1] == "startdata" || columns[1] == "starttime")
                        {
                            addDes = "(起)";
                        }
                        //如果为enddata或endtime,则表示是结束日期/时间段;
                        else if (columns[1] == "enddata" || columns[1] == "endtime")
                        {
                            addDes = "(止)";
                        }
                        else if (columns[1] == "XDBrand" && columns.Length == 3)
                        {
                            addDes = "(品牌)";
                        }
                        else if (columns[1] == "XDSerial" && columns.Length == 3)
                        {
                            addDes = "(车型)";
                        }
                        else if (columns[1] == "YXBrand" && columns.Length == 3)
                        {
                            addDes = "(品牌)";
                        }
                        else if (columns[1] == "YXSerial" && columns.Length == 3)
                        {
                            addDes = "(车型)";
                        }
                        else if (columns[1] == "CSBrand" && columns.Length == 3)
                        {
                            addDes = "(品牌)";
                        }
                        else if (columns[1] == "CSSerial" && columns.Length == 3)
                        {
                            addDes = "(车型)";
                        }

                        //columns.Length == 3:是判断是前面几项 还包括radio、check、select
                        //addDes != string.Empty:如果是时间段/点columns.Length =2,所以加上这个条件
                        if (columns.Length == 3 || addDes != string.Empty)
                        {
                            DataTable dt_TField = BLL.TField.Instance.GetTFieldTableByTFName(columns[0]);
                            tfDesName = dt_TField.Rows[0]["TFDesName"].ToString() + addDes;
                            columnNameArry.Add(tfDesName);

                            //如果导入字段不能为空,则插入tagRed中
                            if (dt_TField.Rows[0]["TFInportIsNull"].ToString() == "0")
                            {
                                tagRed.Add(tfDesName);
                            }
                            excelColumn_Count++;
                        }
                    }

                    else
                    {
                        DataTable dt_TField = BLL.TField.Instance.GetTFieldTableByTFName(columns[0]);
                        tfDesName = dt_TField.Rows[0]["TFDesName"].ToString();
                        columnNameArry.Add(tfDesName);
                        //如果导入字段不能为空,则插入tagRed中
                        if (dt_TField.Rows[0]["TFInportIsNull"].ToString() == "0")
                        {
                            tagRed.Add(tfDesName);
                        }
                        excelColumn_Count++;

                        BLL.Loger.Log4Net.Info("DataTable正在生成,列名:" + tfDesName);
                    }
                }
            }
            #endregion

            #region 设置列的宽度
            int[] colWidth = new int[columnNameArry.Count];
            for (int i = 0; i < columnNameArry.Count; i++)
            {
                colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnNameArry[i]).Length;
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < columnNameArry.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
                    if (intTemp > colWidth[j])
                    {
                        colWidth[j] = intTemp;
                    }
                }
            }
            #endregion

            int rowIndex = 0;
            foreach (DataRow row in dt.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    //if (!string.IsNullOrEmpty(headerText))
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(headerText);

                        ICellStyle headStyle = hssfworkbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = hssfworkbook.CreateFont();
                        font.FontHeightInPoints = 14;
                        font.Boldweight         = 400;
                        font.Color = HSSFColor.RED.index;

                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow;
                        //if (!string.IsNullOrEmpty(headerText))
                        //{
                        //    headerRow = sheet.CreateRow(0);
                        //}
                        //else
                        //{
                        headerRow = sheet.CreateRow(1);
                        //}

                        for (int i = 0; i < columnNameArry.Count; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(columnNameArry[i]);
                            ICellStyle headStyle = hssfworkbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.CENTER;

                            IFont font = hssfworkbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.Boldweight         = 700;
                            if (tagRed.Contains(columnNameArry[i]))
                            {
                                font.Color = HSSFColor.RED.index;
                                headStyle.SetFont(font);
                            }
                            else
                            {
                                headStyle.SetFont(font);
                            }
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            if ((colWidth[i] + 1) * 256 > 30000)
                            {
                                sheet.SetColumnWidth(i, 10000);
                            }
                            else
                            {
                                sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256);
                            }
                        }

                        /*
                         * foreach (DataColumn column in dtSource.Columns)
                         * {
                         *  headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                         *  headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                         *
                         *  //设置列宽
                         *  sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                         * }
                         * */
                    }
                    #endregion
                    //if (!string.IsNullOrEmpty(headerText))
                    //{
                    //    rowIndex = 1;
                    //}
                    //else
                    //{
                    rowIndex = 2;
                    //}
                }
                #endregion

                rowIndex++;
            }


            return(hssfworkbook);
        }
Example #36
0
        //protected void ExportToExcel()
        //{
        //    Response.Clear();
        //    Response.Buffer = true;
        //    Response.AddHeader("content-disposition", "attachment;filename=รายงานคลัง Barcode.xls");
        //    Response.Charset = "";
        //    Response.ContentType = "application/vnd.ms-excel";
        //    using (StringWriter sw = new StringWriter())
        //    {
        //        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //        //To Export all pages
        //        gridStockBarcode.AllowPaging = false;
        //        gridStockBarcode.DataBind();

        //        gridStockBarcode.HeaderRow.BackColor = Color.White;
        //        foreach (TableCell cell in gridStockBarcode.HeaderRow.Cells)
        //        {
        //            cell.BackColor = gridStockBarcode.HeaderStyle.BackColor;
        //        }
        //        foreach (GridViewRow row in gridStockBarcode.Rows)
        //        {
        //            row.BackColor = Color.White;
        //            foreach (TableCell cell in row.Cells)
        //            {
        //                if (row.RowIndex % 2 == 0)
        //                {
        //                    cell.BackColor = gridStockBarcode.AlternatingRowStyle.BackColor;
        //                }
        //                else
        //                {
        //                    cell.BackColor = gridStockBarcode.RowStyle.BackColor;
        //                }
        //                cell.CssClass = "textmode";
        //            }
        //        }

        //        gridStockBarcode.RenderControl(hw);

        //        //style to format numbers to string
        //        string style = @"<style> .textmode { } </style>";
        //        Response.Write(style);
        //        Response.Output.Write(sw.ToString());
        //        Response.Flush();
        //        Response.End();
        //    }
        //}

        public void ExportToExcel(String extension, DataTable dt)
        {
            // dll refered NPOI.dll and NPOI.OOXML

            IWorkbook workbook;

            if (extension == "xlsx")
            {
                workbook = new XSSFWorkbook();
            }
            else if (extension == "xls")
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                throw new Exception("This format is not supported");
            }

            ISheet sheet1 = workbook.CreateSheet("StockBarcode");

            var headerCellStyle = workbook.CreateCellStyle();

            headerCellStyle.FillForegroundColor = HSSFColor.LightOrange.Index;
            headerCellStyle.FillPattern         = FillPattern.SolidForeground;

            var   firstRow  = 0;
            IRow  rowTitle  = sheet1.CreateRow(firstRow);
            ICell cellTitle = rowTitle.CreateCell(0);

            firstRow++;

            var fontTitle = workbook.CreateFont();

            fontTitle.FontName           = "Calibri";
            fontTitle.Boldweight         = (short)FontBoldWeight.Bold;
            fontTitle.FontHeightInPoints = 16;


            var cellStyleTitle = workbook.CreateCellStyle();

            cellStyleTitle.Alignment = HorizontalAlignment.Center;
            cellTitle.CellStyle      = cellStyleTitle;
            cellTitle.CellStyle.SetFont(fontTitle);
            cellTitle.SetCellValue("รายงานคลัง Barcode");

            IRow  rowDate  = sheet1.CreateRow(firstRow);
            ICell cellDate = rowDate.CreateCell(0);

            firstRow++;

            var fontDate = workbook.CreateFont();

            fontDate.FontName           = "Calibri";
            fontDate.Boldweight         = (short)FontBoldWeight.Bold;
            fontDate.FontHeightInPoints = 12;

            var cellStyleDate = workbook.CreateCellStyle();

            cellStyleDate.Alignment = HorizontalAlignment.Center;
            cellDate.CellStyle      = cellStyleDate;
            cellDate.CellStyle.SetFont(fontDate);

            var strDate = "ระหว่างวันที่ " + txtEditStart.Text.Trim() + " ถึง " + txtEditEnd.Text.Trim();

            cellDate.SetCellValue(strDate);


            IRow  rowPrintDate  = sheet1.CreateRow(firstRow);
            ICell cellPrintDate = rowPrintDate.CreateCell(0);

            firstRow++;

            var cellStylePrintDate = workbook.CreateCellStyle();

            cellStylePrintDate.Alignment = HorizontalAlignment.Right;
            cellPrintDate.CellStyle      = cellStylePrintDate;
            cellPrintDate.CellStyle.SetFont(fontDate);

            cellPrintDate.SetCellValue("PrintDate " + DateTime.Now.ToString("dd-MM-yyyy"));

            var range1 = new CellRangeAddress(0, 0, 0, 6);

            sheet1.AddMergedRegion(range1);
            var range2 = new CellRangeAddress(1, 1, 0, 6);

            sheet1.AddMergedRegion(range2);
            var range3 = new CellRangeAddress(2, 2, 0, 6);

            sheet1.AddMergedRegion(range3);

            var listHeader = new[] { "Barcode", "PO No,", "แผนก/คลัง", "วันที่เริ่มต้น", "วันที่แก้ไขล่าสุด", "ผู้แก้ไขล่าสุด", "สถานะ" };
            //make a header row
            IRow row1 = sheet1.CreateRow(firstRow);

            for (int j = 0; j < listHeader.Length; j++)
            {
                ICell cell = row1.CreateCell(j);

                String columnName = listHeader[j];
                cell.SetCellValue(columnName);
                cell.CellStyle = headerCellStyle;
            }


            //loops through data
            // j = 1 ไม่เอา no
            firstRow++;
            int r = 0;

            for (int i = firstRow; r < dt.Rows.Count; i++)
            {
                IRow row = sheet1.CreateRow(i);

                DataRow dr    = dt.Rows[r];
                ICell   cell1 = row.CreateCell(0);
                cell1.SetCellValue(dr["Barcode"].ToString());

                ICell cell2 = row.CreateCell(1);
                cell2.SetCellValue(dr["PONo"].ToString());

                ICell cell3 = row.CreateCell(2);
                cell3.SetCellValue(dr["Department"].ToString());

                ICell cell4 = row.CreateCell(3);
                cell4.SetCellValue(dr["CreateDate"].ToString());

                ICell cell5 = row.CreateCell(4);
                cell5.SetCellValue(dr["LastEditDate"].ToString());

                ICell cell6 = row.CreateCell(5);
                cell6.SetCellValue(dr["LastEditBy"].ToString());

                ICell cell7 = row.CreateCell(6);
                cell7.SetCellValue(dr["Status"].ToString());


                //for (int j = 1; j < dt.Columns.Count; j++)
                //{

                //    ICell cell = row.CreateCell(j);
                //    String columnName = dt.Columns[j].ToString();
                //    cell.SetCellValue(dt.Rows[i][columnName].ToString());

                //    sheet1.AutoSizeColumn(j);
                //}
                r++;
            }

            for (int j = 0; j <= 6; j++)
            {
                sheet1.AutoSizeColumn(j);
            }

            using (var exportData = new MemoryStream())
            {
                Response.Clear();
                workbook.Write(exportData);
                if (extension == "xlsx") //xlsx file format
                {
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "รายงานคลัง StockBarcode.xlsx"));
                    Response.BinaryWrite(exportData.ToArray());
                }
                else if (extension == "xls")  //xls file format
                {
                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "รายงานคลัง StockBarcode.xls"));
                    Response.BinaryWrite(exportData.GetBuffer());
                }
                Response.End();
            }
        }
Example #37
0
        /// <summary>
        /// 导出Excel文件,并合并指定单元格
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="path"></param>
        /// <param name="param"></param>
        public static void ExportExcel(DataTable dt, string path, List <MergeCellParam> param)
        {
            hssfworkbook = new HSSFWorkbook();

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

            hssfworkbook.SummaryInformation = si;


            ISheet sheet1   = hssfworkbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
            IRow   rowTitle = sheet1.CreateRow(0);
            ICell  cellTitle;

            ICellStyle style = hssfworkbook.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;


            for (int m = 0; m < dt.Columns.Count; m++)
            {
                cellTitle           = rowTitle.CreateCell(m);
                cellTitle.CellStyle = style;
                cellTitle.SetCellValue(dt.Columns[m].ColumnName);
            }

            IRow  row;
            ICell cell;

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = sheet1.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell           = row.CreateCell(j);
                        cell.CellStyle = style;
                        string obj = dt.Rows[i][j].ToString();
                        cell.SetCellValue(obj);
                    }
                }
            }

            if (param.Count > 0)
            {
                for (int i = 0; i < param.Count; i++)
                {
                    sheet1.AddMergedRegion(new CellRangeAddress(param[i].FirstRow, param[i].LastRow, param[i].FirstColumn, param[i].LastColumn));
                }
            }

            var file = new FileStream(path, FileMode.Create);

            hssfworkbook.Write(file);
            file.Close();
        }
        private static void CopyRow <T>(ISheet sheet, int templateRowIndex, List <T> dataList, Dictionary <int, string> propertyMapperDict, dynamic propertyGetDict) where T : class
        {
            if (dataList == null || dataList.Count == 0)
            {
                return;
            }

            IRow templateDataRow = sheet.GetRow(templateRowIndex);

            if (templateDataRow == null)
            {
                return;
            }

            List <ExcelMergeCell> mergeCellList = new List <ExcelMergeCell>();

            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == templateDataRow.RowNum)
                {
                    mergeCellList.Add(new ExcelMergeCell()
                    {
                        BeginColumn = cellRangeAddress.FirstColumn, EndColumn = cellRangeAddress.LastColumn
                    });
                }
            }

            int templateDataCellCount = templateDataRow.Cells.Count;

            if (dataList.Count > 1)
            {
                sheet.ShiftRows(templateRowIndex,   //开始行
                                sheet.LastRowNum,   //结束行
                                dataList.Count - 1, //插入行总数
                                true,               //是否复制行高
                                false               //是否重置行高
                                );
            }
            IRow  dataRow      = null;
            ICell templateCell = null;
            ICell dataCell     = null;

            int dataCount     = dataList.Count;
            int dataLastIndex = templateRowIndex + dataCount;

            for (int i = templateRowIndex, j = 0; i < dataLastIndex && j < dataCount; i++, j++)
            {
                dataRow        = sheet.CreateRow(i);
                dataRow.Height = templateDataRow.Height;//复制行高

                for (int colIndex = templateDataRow.FirstCellNum; colIndex < templateDataRow.LastCellNum; colIndex++)
                {
                    templateCell = templateDataRow.GetCell(colIndex);
                    if (templateCell == null)
                    {
                        continue;
                    }

                    dataCell = dataRow.CreateCell(colIndex);
                    if (dataCell == null)
                    {
                        dataCell = templateDataRow.CreateCell(colIndex);
                    }

                    dataCell.CellStyle = templateCell.CellStyle;//赋值单元格格式

                    string templateCellText = templateCell.ToString();
                    if (!templateCellText.StartsWith("&="))
                    {
                        dataCell.SetCellType(templateCell.CellType);

                        if (propertyMapperDict.ContainsKey(colIndex))
                        {
                            object propertyValue = null;
                            if (propertyGetDict != null && propertyGetDict.ContainsKey(propertyMapperDict[colIndex]))
                            {
                                propertyValue = propertyGetDict[propertyMapperDict[colIndex]](dataList[j]);
                            }
                            else
                            {
                                propertyValue = ReflectionHelper.GetPropertyValue(dataList[j], propertyMapperDict[colIndex]);
                            }
                            if (propertyValue != null)
                            {
                                Type propertyType = typeof(T).GetProperty(propertyMapperDict[colIndex]).PropertyType;
                                if (propertyType == typeof(int))
                                {
                                    dataCell.SetCellValue(int.Parse(propertyValue.ToString()));
                                }
                                else if (propertyType == typeof(float) || propertyType == typeof(double))
                                {
                                    dataCell.SetCellValue(double.Parse(propertyValue.ToString()));
                                }
                                else if (propertyType == typeof(DateTime))
                                {
                                    dataCell.SetCellValue(DateTime.Parse(propertyValue.ToString()));
                                }
                                else
                                {
                                    dataCell.SetCellValue(propertyValue.ToString());
                                }
                            }
                        }
                    }
                    else
                    {
                        templateCellText = templateCellText.TrimStart(new char[] { '&', '=' });
                        templateCellText = templateCellText.Replace("i", (dataRow.RowNum + 1).ToString());

                        dataCell.SetCellType(CellType.Formula);
                        dataCell.SetCellFormula(templateCellText);
                    }
                }

                if (i != dataLastIndex - 1 && mergeCellList != null && mergeCellList.Count > 0)
                {
                    foreach (ExcelMergeCell excelMergeCell in mergeCellList)
                    {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(dataRow.RowNum, dataRow.RowNum, excelMergeCell.BeginColumn, excelMergeCell.EndColumn);
                        sheet.AddMergedRegion(cellRangeAddress);
                    }
                }
            }
        }
Example #39
0
        static void FillThePoSheet(ISheet sheet,po po, List<PoItemContentAndState> poitemList)
        {
            sheet.GetRow(0).CreateCell(2).SetCellValue(po.vendorName);
               sheet.GetRow(0).CreateCell(9).SetCellValue(po.contact);
               sheet.GetRow(0).CreateCell(14).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById((int)po.pa));
               sheet.GetRow(1).CreateCell(2).SetCellValue(po.vendorNumber);

               if (po.poDate != null)
               {
               sheet.GetRow(1).CreateCell(5).SetCellValue(po.poDate.ToShortDateString());
               }
               sheet.GetRow(1).CreateCell(9).SetCellValue(po.poNo);
               sheet.GetRow(1).CreateCell(14).SetCellValue(po.paymentTerms);

               sheet.GetRow(2).CreateCell(2).SetCellValue(po.shipMethod);
               sheet.GetRow(2).CreateCell(9).SetCellValue(po.freight);
               sheet.GetRow(2).CreateCell(14).SetCellValue(po.shipToLocation);

               IRow row = sheet.CreateRow(4);
               row.CreateCell(0).SetCellValue(po.billTo);
               row.CreateCell(10).SetCellValue(po.shipTo);

               int itemRowIndex = 9; int totalQty = 0; float totalTotal = 0;int totalQtyRecd = 0;
               foreach (PoItemContentAndState pcs in poitemList)
               {
               IRow itemRow = sheet.CreateRow(itemRowIndex);
               itemRow.CreateCell(0).SetCellValue(poitemList.IndexOf(pcs)+1);
               itemRow.CreateCell(1).SetCellValue(pcs.poItem.partNo);
               itemRow.CreateCell(2).SetCellValue(pcs.poItem.mfg);
               itemRow.CreateCell(3).SetCellValue(pcs.poItem.dc);
               itemRow.CreateCell(4).SetCellValue(pcs.poItem.vendorIntPartNo);
               itemRow.CreateCell(5).SetCellValue(pcs.poItem.coo);
               itemRow.CreateCell(6).SetCellValue(pcs.poItem.qty);
               totalQty += pcs.poItem.qty;

               if (pcs.poItem.qtyRecd != null)
               {
                   itemRow.CreateCell(7).SetCellValue(pcs.poItem.qtyRecd.Value);
                   totalQtyRecd += pcs.poItem.qtyRecd.Value;
               }
               if(pcs.poItem.qtyCorrected!=null)
               itemRow.CreateCell(8).SetCellValue(pcs.poItem.qtyCorrected.Value);

               if(pcs.poItem.qtyAccept!=null)
               itemRow.CreateCell(9).SetCellValue(pcs.poItem.qtyAccept.Value);

               if(pcs.poItem.qtyRejected!=null)
               itemRow.CreateCell(10).SetCellValue(pcs.poItem.qtyRejected.Value);

               if(pcs.poItem.qtyRTV!=null)
                itemRow.CreateCell(11).SetCellValue(pcs.poItem.qtyRTV.Value);

               if(pcs.poItem.qcPending!=null)
               itemRow.CreateCell(12).SetCellValue(pcs.poItem.qcPending.Value);

               itemRow.CreateCell(13).SetCellValue(Enum.GetName(typeof(AmbleClient.Currency), pcs.poItem.currency));
               itemRow.CreateCell(14).SetCellValue(pcs.poItem.unitPrice.Value);
               itemRow.CreateCell(15).SetCellValue(pcs.poItem.qty*pcs.poItem.unitPrice.Value);
               totalTotal += pcs.poItem.qty* pcs.poItem.unitPrice.Value;

               itemRow.CreateCell(16).SetCellValue(pcs.poItem.dockDate.ToShortDateString());

               if(pcs.poItem.receiveDate!=null)
               itemRow.CreateCell(17).SetCellValue(pcs.poItem.receiveDate.Value.ToShortDateString());

               itemRow.CreateCell(18).SetCellValue(pcs.poItem.stepCode);
               itemRow.CreateCell(19).SetCellValue("Unknown");

               itemRowIndex++;

               IRow infoRow = sheet.CreateRow(itemRowIndex);
               infoRow.CreateCell(1).SetCellValue("Note To Vendor >>" + pcs.poItem.noteToVendor);
               sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex, itemRowIndex, 1, 16));

               itemRowIndex++;

               }
               //add total bar
            IRow totalRow=sheet.CreateRow(itemRowIndex);
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,0,4));
                totalRow.CreateCell(5).SetCellValue("Total");
                totalRow.CreateCell(6).SetCellValue(totalQty);
                totalRow.CreateCell(7).SetCellValue(totalQtyRecd);
                totalRow.CreateCell(15).SetCellValue(totalTotal);
        }
        /// <summary>
        /// 按模板生成 Excel 文档
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="templatePath">模板路径</param>
        /// <param name="templateSheetName">模板表单名称</param>
        /// <param name="excelPath">Excel 路径</param>
        /// <param name="dataMatchList">数据匹配,Dictionary&lt;string, object&gt; 或 new {}</param>
        /// <param name="dataList">数据列表</param>
        /// <param name="mergeCellDataList">合并单元格数据列表</param>
        /// <param name="reflectionType">反射类型</param>
        public static void ToExcel <T>(string templatePath, string templateSheetName, string excelPath, object dataMatchList, List <T> dataList, List <ExcelMergeCell> mergeCellDataList = null, ReflectionTypeEnum reflectionType = ReflectionTypeEnum.Expression) where T : class
        {
            Dictionary <string, object> propertyDict = CommonHelper.GetParameterDict(dataMatchList);

            FileStream fileStream = null;
            IWorkbook  workbook   = null;

            try
            {
                using (fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.ReadWrite))
                {
                    workbook = ExcelHelper.ExecuteIWorkBookGet(fileStream);
                    if (workbook == null)
                    {
                        throw new Exception(ExcelHelper.ExcelWorkbookNullException);
                    }
                }

                Dictionary <int, string>        propertyMapperDict = new Dictionary <int, string>();
                List <ExcelTemplateFormulaItem> formulaItemList    = new List <ExcelTemplateFormulaItem>();

                int lastRowIndex   = 0;
                int insertRowIndex = -1;

                ISheet sheet = workbook.GetSheet(templateSheetName);
                if (sheet != null)
                {
                    lastRowIndex = sheet.LastRowNum;
                    for (int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++)
                    {
                        IRow iRow = sheet.GetRow(rowIndex);
                        if (iRow != null)
                        {
                            for (int colIndex = 0; colIndex <= iRow.LastCellNum; colIndex++)
                            {
                                ICell iCell = iRow.GetCell(colIndex);
                                if (iCell != null && !string.IsNullOrEmpty(iCell.ToString()))
                                {
                                    string cellText = iCell.ToString();
                                    if (cellText.StartsWith("$"))
                                    {
                                        cellText = cellText.TrimStart(new char[] { '$' });
                                        if (propertyDict != null && propertyDict.ContainsKey(cellText))
                                        {
                                            iCell.SetCellValue(propertyDict[cellText].ToString());
                                        }
                                    }
                                    else if (cellText.StartsWith("#"))
                                    {
                                        if (insertRowIndex == -1)
                                        {
                                            insertRowIndex = rowIndex;
                                        }

                                        cellText = cellText.TrimStart(new char[] { '#' });
                                        propertyMapperDict.Add(colIndex, cellText);
                                    }
                                    else if (cellText.StartsWith("&="))
                                    {
                                        if (rowIndex != insertRowIndex)
                                        {
                                            formulaItemList.Add(new ExcelTemplateFormulaItem()
                                            {
                                                Cell = iCell, FormulaText = cellText
                                            });
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                if (propertyMapperDict != null && propertyMapperDict.Count > 0 && insertRowIndex != -1)
                {
                    if (dataList != null && dataList.Count > 0)
                    {
                        dynamic propertyGetDict = null;
                        if (reflectionType != ReflectionTypeEnum.Original)
                        {
                            propertyGetDict = ReflectionExtendHelper.PropertyGetCallDict <T>(reflectionType);
                        }

                        CopyRow(sheet, insertRowIndex, dataList, propertyMapperDict, propertyGetDict);

                        if (formulaItemList != null && formulaItemList.Count > 0)
                        {
                            foreach (ExcelTemplateFormulaItem formulaItem in formulaItemList)
                            {
                                formulaItem.FormulaText = formulaItem.FormulaText.TrimStart(new char[] { '&', '=' });
                                formulaItem.FormulaText = formulaItem.FormulaText.Replace("_dataBegin", (insertRowIndex + 1).ToString());
                                formulaItem.FormulaText = formulaItem.FormulaText.Replace("_dataEnd", (insertRowIndex + dataList.Count).ToString());

                                formulaItem.Cell.SetCellFormula(formulaItem.FormulaText);
                                formulaItem.Cell.SetCellType(CellType.Formula);
                            }
                        }
                    }
                }

                sheet.ForceFormulaRecalculation = true;

                if (mergeCellDataList != null && mergeCellDataList.Count > 0)
                {
                    foreach (ExcelMergeCell cellItem in mergeCellDataList)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(insertRowIndex + cellItem.BeginRow, insertRowIndex + cellItem.EndRow, cellItem.BeginColumn, cellItem.EndColumn));
                    }
                }

                if (System.IO.File.Exists(excelPath))
                {
                    System.IO.File.Delete(excelPath);
                }
                using (fileStream = new FileStream(excelPath, FileMode.Create, FileAccess.ReadWrite))
                {
                    workbook.Write(fileStream);
                }
                workbook.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (fileStream != null)
                {
                    fileStream.Close();
                }
                if (workbook != null)
                {
                    workbook.Close();
                }
            }
        }
Example #41
0
        private void MergeReportExcelColum(List<OutStoreModel> outStoreModels, ISheet sheet)
        {
            var preModel = outStoreModels[0];
            int row_start = 2;
            int row_end = 2;

            for (int i = 1; i < outStoreModels.Count; i++)
            {
                if (preModel.Department == outStoreModels[i].Department)
                {
                    row_end++;
                }
                else
                {
                    if (row_end > row_start)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
                        sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 6, 6));

                        row_start = row_end;
                    }
                    row_end++;
                    row_start++;
                }

                preModel = outStoreModels[i];
            }

            if (row_end > row_start)
            {
                sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 6, 6));
            }
        }
Example #42
0
        public void Main()
        {
            InitializeWorkbook();

            Dictionary <String, ICellStyle> styles = CreateStyles(hssfworkbook);

            ISheet      sheet      = hssfworkbook.CreateSheet("Timesheet");
            IPrintSetup printSetup = sheet.PrintSetup;

            printSetup.Landscape     = true;
            sheet.FitToPage          = (true);
            sheet.HorizontallyCenter = (true);

            //title row
            IRow titleRow = sheet.CreateRow(0);

            titleRow.HeightInPoints = (45);
            ICell titleCell = titleRow.CreateCell(0);

            titleCell.SetCellValue("Weekly Timesheet");
            titleCell.CellStyle = (styles["title"]);
            sheet.AddMergedRegion(CellRangeAddress.ValueOf("$A$1:$L$1"));

            //header row
            IRow headerRow = sheet.CreateRow(1);

            headerRow.HeightInPoints = (40);
            ICell headerCell;

            for (int i = 0; i < titles.Length; i++)
            {
                headerCell = headerRow.CreateCell(i);
                headerCell.SetCellValue(titles[i]);
                headerCell.CellStyle = (styles["header"]);
            }


            int rownum = 2;

            for (int i = 0; i < 10; i++)
            {
                IRow row = sheet.CreateRow(rownum++);
                for (int j = 0; j < titles.Length; j++)
                {
                    ICell cell = row.CreateCell(j);
                    if (j == 9)
                    {
                        //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                        String reference = "C" + rownum + ":I" + rownum;
                        cell.CellFormula = ("SUM(" + reference + ")");
                        cell.CellStyle   = (styles["formula"]);
                    }
                    else if (j == 11)
                    {
                        cell.CellFormula = ("J" + rownum + "-K" + rownum);
                        cell.CellStyle   = (styles["formula"]);
                    }
                    else
                    {
                        cell.CellStyle = (styles["cell"]);
                    }
                }
            }

            //row with totals below
            IRow sumRow = sheet.CreateRow(rownum++);

            sumRow.HeightInPoints = (35);
            ICell cell1 = sumRow.CreateCell(0);

            cell1.CellStyle = (styles["formula"]);

            ICell cell2 = sumRow.CreateCell(1);

            cell2.SetCellValue("Total Hrs:");
            cell2.CellStyle = (styles["formula"]);

            for (int j = 2; j < 12; j++)
            {
                ICell  cell      = sumRow.CreateCell(j);
                String reference = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
                cell.CellFormula = ("SUM(" + reference + ")");
                if (j >= 9)
                {
                    cell.CellStyle = (styles["formula_2"]);
                }
                else
                {
                    cell.CellStyle = (styles["formula"]);
                }
            }

            rownum++;
            sumRow = sheet.CreateRow(rownum++);
            sumRow.HeightInPoints = 25;
            ICell cell3 = sumRow.CreateCell(0);

            cell3.SetCellValue("Total Regular Hours");
            cell3.CellStyle       = styles["formula"];
            cell3                 = sumRow.CreateCell(1);
            cell3.CellFormula     = ("L13");
            cell3.CellStyle       = styles["formula_2"];
            sumRow                = sheet.CreateRow(rownum++);
            sumRow.HeightInPoints = (25);
            cell3                 = sumRow.CreateCell(0);
            cell3.SetCellValue("Total Overtime Hours");
            cell3.CellStyle   = styles["formula"];
            cell3             = sumRow.CreateCell(1);
            cell3.CellFormula = ("K13");
            cell3.CellStyle   = styles["formula_2"];

            //set sample data
            for (int i = 0; i < sample_data.GetLength(0); i++)
            {
                IRow row = sheet.GetRow(2 + i);
                for (int j = 0; j < sample_data.GetLength(1); j++)
                {
                    if (sample_data[i, j] == null)
                    {
                        continue;
                    }

                    if (sample_data[i, j] is String)
                    {
                        row.GetCell(j).SetCellValue((String)sample_data[i, j]);
                    }
                    else
                    {
                        row.GetCell(j).SetCellValue((Double)sample_data[i, j]);
                    }
                }
            }

            //finally set column widths, the width is measured in units of 1/256th of a character width
            sheet.SetColumnWidth(0, 30 * 256); //30 characters wide
            for (int i = 2; i < 9; i++)
            {
                sheet.SetColumnWidth(i, 6 * 256); //6 characters wide
            }
            sheet.SetColumnWidth(10, 10 * 256);   //10 characters wide


            WriteToFile();
        }
Example #43
0
        /// <summary>
        /// 设置excel模版错误信息
        /// </summary>
        /// <param name="sheet">数据标签</param>
        /// <param name="rowindex">错误信息显示行</param>
        /// <param name="msg">错误信息</param>
        public static void SetTemplateErrorMsg(ISheet sheet, int rowindex, string msg)
        {
            IRow row = sheet.GetRow(rowindex);
            row = sheet.CreateRow(rowindex);
            if (row != null && !string.IsNullOrEmpty(msg))
            {
                sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, row.LastCellNum));

                ICell cell = row.GetCell(0);
                if (cell == null)
                {
                    cell = row.CreateCell(0);
                }
                ICellStyle cellStyle = sheet.Workbook.CreateCellStyle();
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.LEFT;
                IFont font = sheet.Workbook.CreateFont();
                font.FontHeightInPoints = 12;
                font.Color = HSSFColor.RED.index;
                cellStyle.SetFont(font);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(msg);
            }
        }
        /// <summary>
        /// DataTable导出到Excel的MemoryStream Export()
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
        public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();
            Type         type     = typeof(T);

            PropertyInfo[] properties = type.GetProperties();
            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = "Yingmei"; //填加xls文件作者信息
                si.ApplicationName          = "映美Me云打印"; //填加xls文件创建程序信息
                si.LastAuthor               = "Yingmei"; //填加xls文件最后保存者信息
                si.Comments                 = "Yingmei"; //填加xls文件作者信息
                si.Title                    = "标题信息";    //填加xls文件标题信息
                si.Subject                  = "主题信息";    //填加文件主题信息
                si.CreateDateTime           = System.DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            #region 设置标题样式
            ICellStyle   headStyle      = workbook.CreateCellStyle();
            int[]        arrColWidth    = new int[properties.Length];
            string[]     arrColName     = new string[properties.Length];     //列名
            ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
            headStyle.Alignment = HorizontalAlignment.Center;                // ------------------
            if (excelConfig.Background != new Color())
            {
                if (excelConfig.Background != new Color())
                {
                    headStyle.FillPattern         = FillPattern.SolidForeground;
                    headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
                }
            }
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig.TitlePoint;
            if (excelConfig.ForeColor != new Color())
            {
                font.Color = GetXLColour(workbook, excelConfig.ForeColor);
            }
            font.Boldweight = 700;
            headStyle.SetFont(font);
            #endregion

            #region 列头及样式
            ICellStyle cHeadStyle = workbook.CreateCellStyle();
            cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
            IFont cfont = workbook.CreateFont();
            cfont.FontHeightInPoints = excelConfig.HeadPoint;
            cHeadStyle.SetFont(cfont);
            #endregion

            #region 设置内容单元格样式
            int i = 0;
            foreach (PropertyInfo column in properties)
            {
                ICellStyle columnStyle = workbook.CreateCellStyle();
                columnStyle.Alignment = HorizontalAlignment.Center;
                arrColWidth[i]        = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
                arrColName[i]         = column.Name;

                if (excelConfig.ColumnEntity != null)
                {
                    ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
                    if (columnentity != null)
                    {
                        arrColName[i] = columnentity.ExcelColumn;
                        if (columnentity.Width != 0)
                        {
                            arrColWidth[i] = columnentity.Width;
                        }
                        if (columnentity.Background != new Color())
                        {
                            if (columnentity.Background != new Color())
                            {
                                columnStyle.FillPattern         = FillPattern.SolidForeground;
                                columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
                            }
                        }
                        if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
                        {
                            IFont columnFont = workbook.CreateFont();
                            columnFont.FontHeightInPoints = 10;
                            if (columnentity.Font != null)
                            {
                                columnFont.FontName = columnentity.Font;
                            }
                            if (columnentity.Point != 0)
                            {
                                columnFont.FontHeightInPoints = columnentity.Point;
                            }
                            if (columnentity.ForeColor != new Color())
                            {
                                columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
                            }
                            columnStyle.SetFont(font);
                        }
                    }
                }
                arryColumStyle[i] = columnStyle;
                i++;
            }
            #endregion

            #region 填充数据

            #endregion
            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            int rowIndex = 0;
            foreach (T item in lists)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        if (excelConfig.Title != null)
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            if (excelConfig.TitleHeight != 0)
                            {
                                headerRow.Height = (short)(excelConfig.TitleHeight * 20);
                            }
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
                        }
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1);
                        #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出
                        int headIndex = 0;
                        foreach (PropertyInfo column in properties)
                        {
                            headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
                            headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
                            //设置列宽
                            sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
                            headIndex++;
                        }
                        #endregion
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                int  ordinal = 0;
                foreach (PropertyInfo column in properties)
                {
                    ICell newCell = dataRow.CreateCell(ordinal);
                    newCell.CellStyle = arryColumStyle[ordinal];
                    string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
                    SetCell(newCell, dateStyle, column.PropertyType, drValue);
                    ordinal++;
                }
                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }
Example #45
0
        public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
        {
            if (sourceRowIndex == targetRowIndex)
                throw new ArgumentException("sourceIndex and targetIndex cannot be same");
            // Get the source / new row
            IRow newRow = sheet.GetRow(targetRowIndex);
            IRow sourceRow = sheet.GetRow(sourceRowIndex);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
            }
            else
            {
                newRow = sheet.CreateRow(targetRowIndex);
            }

            // Loop through source columns to add to new row
            for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    continue;
                }
                ICell newCell = newRow.CreateCell(i);

                if (oldCell.CellStyle != null)
                {
                    // apply style from old cell to new cell 
                    newCell.CellStyle = oldCell.CellStyle;
                }

                // If there is a cell comment, copy
                if (oldCell.CellComment != null)
                {
                    newCell.CellComment = oldCell.CellComment;
                }

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null)
                {
                    newCell.Hyperlink = oldCell.Hyperlink;
                }

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.Blank:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.Boolean:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.Error:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.Formula:
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.Numeric:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.String:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                            (newRow.RowNum +
                                    (cellRangeAddress.LastRow - cellRangeAddress.FirstRow
                                            )),
                            cellRangeAddress.FirstColumn,
                            cellRangeAddress.LastColumn);
                    sheet.AddMergedRegion(newCellRangeAddress);
                }
            }
            return newRow;
        }
Example #46
0
        /// <summary>
        /// 向EXCL文件写数据
        /// </summary>
        /// <param name="filePath"></param>
        public static void WriteToExcl(string filePath)
        {
            //创建工作薄
            IWorkbook wb;
            string    extension = System.IO.Path.GetExtension(filePath);

            //根据指定的文件格式创建对应的类
            if (extension.Equals(".xls"))
            {
                wb = new HSSFWorkbook();
            }
            else
            {
                wb = new XSSFWorkbook();
            }

            ICellStyle style1 = wb.CreateCellStyle();                              //样式

            style1.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
            style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
            //设置边框
            style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.WrapText     = true;               //自动换行

            ICellStyle style2 = wb.CreateCellStyle(); //样式
            IFont      font1  = wb.CreateFont();      //字体

            font1.FontName   = "楷体";
            font1.Color      = HSSFColor.Red.Index;          //字体颜色
            font1.Boldweight = (short)FontBoldWeight.Normal; //字体加粗样式
            style2.SetFont(font1);                           //样式里的字体设置具体的字体样式
            //设置背景色
            style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            style2.FillPattern         = FillPattern.SolidForeground;
            style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            style2.Alignment           = NPOI.SS.UserModel.HorizontalAlignment.Left;  //文字水平对齐方式
            style2.VerticalAlignment   = NPOI.SS.UserModel.VerticalAlignment.Center;  //文字垂直对齐方式

            ICellStyle dateStyle = wb.CreateCellStyle();                              //样式

            dateStyle.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
            dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
            //设置数据显示格式
            IDataFormat dataFormatCustom = wb.CreateDataFormat();

            dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");

            //创建一个表单
            ISheet sheet = wb.CreateSheet("Sheet0");

            //设置列宽
            int[] columnWidth = { 10, 10, 20, 10 };
            for (int i = 0; i < columnWidth.Length; i++)
            {
                //设置列宽度,256*字符数,因为单位是1/256个字符
                sheet.SetColumnWidth(i, 256 * columnWidth[i]);
            }

            //测试数据
            int rowCount = 3, columnCount = 4;

            object[,] data =
            {
                { "列0", "列1", "列2",         "列3"         },
                { "",    400,          5.2,         6.01 },
                { "",   true, "2014-07-02", DateTime.Now }
                //日期可以直接传字符串,NPOI会自动识别
                //如果是DateTime类型,则要设置CellStyle.DataFormat,否则会显示为数字
            };

            IRow  row;
            ICell cell;

            for (int i = 0; i < rowCount; i++)
            {
                row = sheet.CreateRow(i);//创建第i行
                for (int j = 0; j < columnCount; j++)
                {
                    cell           = row.CreateCell(j);//创建第j列
                    cell.CellStyle = j % 2 == 0 ? style1 : style2;
                    //根据数据类型设置不同类型的cell
                    object obj = data[i, j];
                    SetCellValue(cell, data[i, j]);
                    //如果是日期,则设置日期显示的格式
                    if (obj.GetType() == typeof(DateTime))
                    {
                        cell.CellStyle = dateStyle;
                    }
                    //如果要根据内容自动调整列宽,需要先setCellValue再调用
                    //sheet.AutoSizeColumn(j);
                }
            }

            //合并单元格,如果要合并的单元格中都有数据,只会保留左上角的
            //CellRangeAddress(0, 2, 0, 0),合并0-2行,0-0列的单元格
            CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);

            sheet.AddMergedRegion(region);

            try
            {
                FileStream fs = File.OpenWrite(filePath);
                wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
                fs.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
Example #47
0
 private static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
 {
     CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
     sheet.AddMergedRegion(cellRangeAddress);
 }
Example #48
0
 /// <summary>
 /// 合并单元格
 /// </summary>
 /// <param name="tb">工作薄</param>
 /// <param name="startRow">开始行</param>
 /// <param name="endRow">结束行</param>
 /// <param name="startCol">开始列</param>
 /// <param name="endCol">结束列</param>
 /// <returns></returns>
 public static ICell MergedRegion(ISheet tb, int startRow, int endRow, int startCol, int endCol)
 {
     IRow row = tb.GetRow(startRow) == null
         ? tb.CreateRow(startRow)
         : tb.GetRow(startRow);
     ICell cell = row.GetCell(startCol) == null
         ? row.CreateCell(startCol)
         : row.GetCell(startCol);
     row.Height = 30 * 20;
     if (startRow != endRow || startCol != endCol)
     {
         var i = tb.AddMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
     }
     return cell;
 }