コード例 #1
0
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            _cusName   = DTRequest.GetString("txtCusName");
            _cid       = DTRequest.GetString("hCusId");
            _method    = DTRequest.GetFormString("ddlmethod");
            _isconfirm = DTRequest.GetFormString("ddlisConfirm");
            _sforedate = DTRequest.GetFormString("txtsforedate");
            _eforedate = DTRequest.GetFormString("txteforedate");
            _sdate     = DTRequest.GetFormString("txtsdate");
            _edate     = DTRequest.GetFormString("txtedate");
            _num       = DTRequest.GetFormString("txtNum");
            _chk       = DTRequest.GetFormString("txtChk");
            BLL.ReceiptPay bll = new BLL.ReceiptPay();
            DataTable      dt  = bll.GetList(this.pageSize, this.page, "rp_type=1 " + CombSqlTxt(), "isnull(rp_date,'3000-01-01') desc,isnull(pm_sort,-1) asc,rp_id desc", out this.totalCount, out _tmoney, out _tunmoney, false).Tables[0];

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=收款通知列表.xlsx"); //HttpUtility.UrlEncode(fileName));
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet       sheet        = hssfworkbook.CreateSheet("明细");
            IFont        font         = hssfworkbook.CreateFont();

            font.Boldweight         = short.MaxValue;
            font.FontHeightInPoints = 11;

            #region 表格样式
            //设置单元格的样式:水平垂直对齐居中
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            cellStyle.Alignment         = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.BorderBottom      = BorderStyle.Thin;
            cellStyle.BorderLeft        = BorderStyle.Thin;
            cellStyle.BorderRight       = BorderStyle.Thin;
            cellStyle.BorderTop         = BorderStyle.Thin;
            cellStyle.BottomBorderColor = HSSFColor.Black.Index;
            cellStyle.LeftBorderColor   = HSSFColor.Black.Index;
            cellStyle.RightBorderColor  = HSSFColor.Black.Index;
            cellStyle.TopBorderColor    = HSSFColor.Black.Index;
            cellStyle.WrapText          = true;//自动换行

            //设置表头的样式:水平垂直对齐居中,加粗
            ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
            titleCellStyle.Alignment           = HorizontalAlignment.Center;
            titleCellStyle.VerticalAlignment   = VerticalAlignment.Center;
            titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
            titleCellStyle.FillPattern         = FillPattern.SparseDots;        //图案样式
            titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
            //设置边框
            titleCellStyle.BorderBottom      = BorderStyle.Thin;
            titleCellStyle.BorderLeft        = BorderStyle.Thin;
            titleCellStyle.BorderRight       = BorderStyle.Thin;
            titleCellStyle.BorderTop         = BorderStyle.Thin;
            titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
            titleCellStyle.LeftBorderColor   = HSSFColor.Black.Index;
            titleCellStyle.RightBorderColor  = HSSFColor.Black.Index;
            titleCellStyle.TopBorderColor    = HSSFColor.Black.Index;
            //设置字体
            titleCellStyle.SetFont(font);
            #endregion
            //表头
            IRow headRow = sheet.CreateRow(0);
            headRow.HeightInPoints = 25;

            headRow.CreateCell(0).SetCellValue("收款对象");
            headRow.CreateCell(1).SetCellValue("凭证");
            headRow.CreateCell(2).SetCellValue("收款内容");
            headRow.CreateCell(3).SetCellValue("收款金额");
            headRow.CreateCell(4).SetCellValue("未分配金额");
            headRow.CreateCell(5).SetCellValue("预收日期");
            headRow.CreateCell(6).SetCellValue("收款方式");
            headRow.CreateCell(7).SetCellValue("实收日期");
            headRow.CreateCell(8).SetCellValue("申请人");
            headRow.CreateCell(9).SetCellValue("确认收款");

            headRow.GetCell(0).CellStyle = titleCellStyle;
            headRow.GetCell(1).CellStyle = titleCellStyle;
            headRow.GetCell(2).CellStyle = titleCellStyle;
            headRow.GetCell(3).CellStyle = titleCellStyle;
            headRow.GetCell(4).CellStyle = titleCellStyle;
            headRow.GetCell(5).CellStyle = titleCellStyle;
            headRow.GetCell(6).CellStyle = titleCellStyle;
            headRow.GetCell(7).CellStyle = titleCellStyle;
            headRow.GetCell(8).CellStyle = titleCellStyle;
            headRow.GetCell(9).CellStyle = titleCellStyle;

            sheet.SetColumnWidth(0, 15 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 20 * 256);
            sheet.SetColumnWidth(5, 15 * 256);
            sheet.SetColumnWidth(6, 20 * 256);
            sheet.SetColumnWidth(7, 20 * 256);
            sheet.SetColumnWidth(8, 20 * 256);
            sheet.SetColumnWidth(9, 20 * 256);

            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);
                    row.HeightInPoints = 22;
                    row.CreateCell(0).SetCellValue(dt.Rows[i]["c_name"].ToString() + (Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isExpect"]), false) ? "[预]" : ""));
                    row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["ce_num"]));
                    row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_content"]));
                    row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_money"]));
                    row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["undistribute"]));
                    row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_foredate"]).Value.ToString("yyyy-MM-dd"));
                    row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"]));
                    row.CreateCell(7).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd"));
                    row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_personName"]));
                    row.CreateCell(9).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isConfirm"]), false) ? "已收款" : "待收款");

                    row.GetCell(0).CellStyle = cellStyle;
                    row.GetCell(1).CellStyle = cellStyle;
                    row.GetCell(2).CellStyle = cellStyle;
                    row.GetCell(3).CellStyle = cellStyle;
                    row.GetCell(4).CellStyle = cellStyle;
                    row.GetCell(5).CellStyle = cellStyle;
                    row.GetCell(6).CellStyle = cellStyle;
                    row.GetCell(7).CellStyle = cellStyle;
                    row.GetCell(8).CellStyle = cellStyle;
                    row.GetCell(9).CellStyle = cellStyle;
                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }
コード例 #2
0
ファイル: IEnumerableEx.cs プロジェクト: wxytkzc/GitHubCode
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dtSource"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="writeColumnName"></param>
        /// <param name="excelType"></param>
        public static void WriteInToExcel <T>(this IEnumerable <T> dtSource, string fileName, string sheetName, bool writeColumnName = true, ExcelType excelType = ExcelType.XLS)
        {
            FileStream fs       = null;
            IWorkbook  workbook = null;
            ISheet     sheet    = null;

            try
            {
                #region 初始化
                if (excelType == ExcelType.XLS)
                {
                    workbook = new HSSFWorkbook();
                    if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
                    {
                        fileName = fileName + ".xls";
                    }
                }
                else
                {
                    workbook = new XSSFWorkbook();
                    if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
                    {
                        fileName = fileName + ".xlsx";
                    }
                }

                if (workbook != null)
                {
                    if (sheet == null)
                    {
                        sheet = workbook.CreateSheet(sheetName);
                    }
                }
                fs = new FileStream(fileName + ".", FileMode.OpenOrCreate, FileAccess.ReadWrite);
                #endregion

                #region 写列名称
                if (writeColumnName)
                {
                    IRow row         = sheet.CreateRow(0);
                    int  columnIndex = 0;
                    typeof(T).GetProperties().AsEnumerable().ToList().ForEach(x =>
                    {
                        row.CreateCell(columnIndex).SetCellValue(x.Name != null ? x.Name : string.Empty);
                        columnIndex++;
                    });
                }
                #endregion

                #region 写内容
                int i = writeColumnName ? 1 : 0;
                dtSource.ToList().ForEach(x =>
                {
                    IRow row = sheet.CreateRow(i);
                    int m    = 0;
                    typeof(T).GetProperties().AsEnumerable().ToList().ForEach(p =>
                    {
                        var value = x.GetType().GetProperty(p.Name).GetValue(x, null);
                        row.CreateCell(m).SetCellValue(TypeConvert.ToString(p.PropertyType, value != null ? value : string.Empty));
                        m++;
                    });
                    i++;
                });
                #endregion

                workbook.Write(fs); //写入到excel
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                fs.Dispose();
                fs.Close();
            }
        }
コード例 #3
0
ファイル: ExcelHelper.cs プロジェクト: yunchenglk/-
        /// <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++;
                    if (colint < excelConfig.ColumnEntity.Count - 1)
                    {
                        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                   = "刘晓雷";  //填加xls文件作者信息
                si.ApplicationName          = "力软信息"; //填加xls文件创建程序信息
                si.LastAuthor               = "刘晓雷";  //填加xls文件最后保存者信息
                si.Comments                 = "刘晓雷";  //填加xls文件作者信息
                si.Title                    = "标题信息"; //填加xls文件标题信息
                si.Subject                  = "主题信息"; //填加文件主题信息
                si.CreateDateTime           = System.DateTime.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)
                {
                    ColumnModel 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

            int rowIndex = 0;

            #region 表头及样式
            if (excelConfig.Title != null)
            {
                IRow headerRow = sheet.CreateRow(rowIndex);
                rowIndex++;
                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(rowIndex);
                rowIndex++;
                #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);

                    int colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
                    if (colWidth < 255 * 256)
                    {
                        sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth);
                    }
                    else
                    {
                        sheet.SetColumnWidth(column.Ordinal, 6000);
                    }
                }
                #endregion
            }
            #endregion

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

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535)
                {
                    sheet    = workbook.CreateSheet();
                    rowIndex = 0;
                    #region 表头及样式
                    {
                        if (excelConfig.Title != null)
                        {
                            IRow headerRow = sheet.CreateRow(rowIndex);
                            rowIndex++;
                            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(rowIndex);
                        rowIndex++;
                        #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
                }
                #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);
            }
        }
コード例 #4
0
        public ActionResult XLS(string List)
        {
            var stream   = new MemoryStream();
            var workbook = new HSSFWorkbook();

            var ids    = List.Split <int>(";");
            var orders = DB.Orders.Where(x => ids.Contains(x.ID) && x.Shop != null && x.Shop.Owner == CurrentUser.ShopOwnerID).ToList();

            foreach (var order in orders)
            {
                var worksheet = workbook.CreateSheet("Заказ №" + order.OrderNumberOrID.Replace("[", "@").Replace("]", "@"));
                var header    = worksheet.CreateRow(0);
                header.CreateCell(0).SetCellValue("Информация о заказе:");

                header = worksheet.CreateRow(1);
                header.CreateCell(0).SetCellValue("Номер заказа");
                header.CreateCell(1).SetCellValue(order.OrderNumberOrID);

                header = worksheet.CreateRow(2);
                header.CreateCell(0).SetCellValue("Статус заказа");
                header.CreateCell(1).SetCellValue(order.StatusText);

                header = worksheet.CreateRow(3);
                header.CreateCell(0).SetCellValue("Пометка \"Важный\"");
                header.CreateCell(1).SetCellValue(order.IsImportant.ToYesNoStatus());

                header = worksheet.CreateRow(4);
                header.CreateCell(0).SetCellValue("Дата создания");
                header.CreateCell(1).SetCellValue(order.CreateDate.ToString("dd.MM.yyyy HH:mm:ss"));

                header = worksheet.CreateRow(5);
                header.CreateCell(0).SetCellValue("Комментарий");
                header.CreateCell(1).SetCellValue(order.Warning);

                header = worksheet.CreateRow(6);
                header.CreateCell(0).SetCellValue("");

                if (order.Consumer != null)
                {
                    header = worksheet.CreateRow(7);
                    header.CreateCell(0).SetCellValue("Информация о покупателе:");

                    header = worksheet.CreateRow(8);
                    header.CreateCell(0).SetCellValue("ФИО");
                    header.CreateCell(1).SetCellValue(order.Consumer.FullName);

                    header = worksheet.CreateRow(9);
                    header.CreateCell(0).SetCellValue("Телефон");
                    header.CreateCell(1).SetCellValue(order.Consumer.Phone);

                    header = worksheet.CreateRow(10);
                    header.CreateCell(0).SetCellValue("");
                }

                if (order.OrderedProducts.Any())
                {
                    header = worksheet.CreateRow(11);
                    header.CreateCell(0).SetCellValue("Заказанные товары:");


                    header = worksheet.CreateRow(12);
                    header.CreateCell(0).SetCellValue("Название");
                    header.CreateCell(1).SetCellValue("Артикул");
                    header.CreateCell(2).SetCellValue("Цена");
                    header.CreateCell(3).SetCellValue("Количество");
                    header.CreateCell(4).SetCellValue("Вес");
                    int index = 5;

                    foreach (var ch in order.CharList)
                    {
                        header.CreateCell(index).SetCellValue(ch.Name);
                        index++;
                    }


                    int msi = 13;
                    foreach (var product in order.OrderedProducts)
                    {
                        header = worksheet.CreateRow(msi);
                        header.CreateCell(0).SetCellValue(product.Product.Name);
                        header.CreateCell(1).SetCellValue(product.Product.Article);
                        header.CreateCell(2).SetCellValue(product.Price.ToString());
                        header.CreateCell(3).SetCellValue(product.Amount);
                        header.CreateCell(4).SetCellValue(product.Product.Weight.ToString());
                        index = 5;
                        foreach (var ch in order.CharList)
                        {
                            header.CreateCell(index)
                            .SetCellValue(product.ProductChars.Any(x => x.Name == ch.Name)
                                    ? product.ProductChars.First(x => x.Name == ch.Name).Value
                                    : "");
                            index++;
                        }


                        msi++;
                    }
                }
            }


            workbook.Write(stream);

            return(File(stream.ToArray(), MIMETypeWrapper.GetMIME("xls"),
                        "Order_" + List.Trim(';').Replace(";", "_") + ".xls"));
        }
コード例 #5
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="html">表头html结构</param>
        /// <param name="dateFormat">时间格式(默认:yyyy-MM-dd)</param>
        private static MemoryStream DataTableToComplexExcel(DataTable dtSource, string html, string dateFormat)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet    sheet    = (HSSFSheet)workbook.CreateSheet();

            HSSFCellStyle  dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFDataFormat format    = (HSSFDataFormat)workbook.CreateDataFormat();

            dateStyle.DataFormat = format.GetFormat(dateFormat);

            //设置单元格样式及字体
            HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFFont      font      = (HSSFFont)workbook.CreateFont();

            font.FontHeightInPoints = 10;                             //字体大小
            font.Boldweight         = 700;                            //字体粗细
            cellStyle.SetFont(font);
            cellStyle.Alignment         = HorizontalAlignment.Center; //水平居中
            cellStyle.VerticalAlignment = VerticalAlignment.Center;   //垂直居中

            //取得列宽
            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 = (HSSFSheet)workbook.CreateSheet();
                    }

                    #region 列头及样式
                    {
                        //获取页面html并对tr进行筛选
                        MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);

                        //写在tr循环中
                        for (int i = 0; i < rowCollection.Count; i++)
                        {
                            HSSFRow rowTitle   = (HSSFRow)sheet.CreateRow(i);
                            string  rowContent = rowCollection[i].Value;

                            //对td进行筛选
                            MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);

                            //遍历td
                            for (int j = 0; j < columnCollection.Count; j++)
                            {
                                var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\".*?class=\"(?<class>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
                                if (match.Success)
                                {
                                    int    rowspan = Convert.ToInt32(match.Groups["row"].Value);  //表格跨行
                                    int    colspan = Convert.ToInt32(match.Groups["col"].Value);  //表格跨列
                                    int    rowNo   = Convert.ToInt32(match.Groups["row1"].Value); //所在行
                                    int    colNo   = Convert.ToInt32(match.Groups["col1"].Value); //所在列
                                    string value   = match.Groups["value"].Value;

                                    if (colspan == 1)                          //判断是否跨列
                                    {
                                        var cell = rowTitle.CreateCell(colNo); //创建列
                                        cell.SetCellValue(value);              //设置列的值
                                        cell.CellStyle = cellStyle;
                                        if (value.Length > 0)
                                        {
                                            int width = value.Length * 25 / 6;
                                            if (width > 255)
                                            {
                                                width = 250;
                                            }
                                            sheet.SetColumnWidth(colNo, width * 256);
                                        }
                                    }
                                    //判断是否跨行、跨列
                                    if (rowspan > 1 || colspan > 1)
                                    {
                                        int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0;
                                        if (rowspan > 1)//跨行
                                        {
                                            firstRow = rowNo;
                                            lastRow  = firstRow + rowspan - 1;
                                        }
                                        else
                                        {
                                            firstRow = lastRow = i;
                                        }
                                        if (colspan > 1)//跨列
                                        {
                                            firstCol = colNo;
                                            int cols = colNo + colspan;
                                            for (; colNo < cols; colNo++)
                                            {
                                                var cell = rowTitle.CreateCell(colNo);
                                                cell.SetCellValue(value);
                                                cell.CellStyle = cellStyle;
                                            }
                                            lastCol = colNo - 1;
                                        }
                                        else
                                        {
                                            firstCol = lastCol = colNo;
                                        }

                                        //设置起始行数,结束行数,起始列数,结束列数
                                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
                                    }
                                }
                            }
                        }
                        rowIndex = rowCollection.Count;
                    }
                    #endregion
                }
                #endregion

                #region 填充内容
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                    newCell.CellStyle.Alignment         = HorizontalAlignment.Center; //水平居中;
                    newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center;   //垂直居中

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

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

                    case "System.DateTime":    //日期类型
                        System.DateTime dateV;
                        System.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();
                return(ms);
            }
        }
コード例 #6
0
 public void Init(object data)
 {
     workbook = new HSSFWorkbook();
     sheet    = workbook.CreateSheet("sheet1");
     sheet.DefaultRowHeight = 200 * 20;
 }
コード例 #7
0
        //导入
        protected void btnimport_Click(object sender, EventArgs e)
        {
            List <string> list    = new List <string>();
            string        sqlifsc = "select * from OM_GZHSB where GZ_YEARMONTH='" + dplYear.SelectedValue.ToString().Trim() + "-" + dplMoth.SelectedValue.ToString().Trim() + "' and OM_GZSCBZ='1'";

            System.Data.DataTable dtifsc = DBCallCommon.GetDTUsingSqlText(sqlifsc);
            if (dtifsc.Rows.Count > 0)
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('该月已生成工资,不能导入!!!');", true);
                ModalPopupExtenderSearch.Hide();
                return;
            }
            string FilePath = @"E:\考核成绩记录表\";

            if (!Directory.Exists(FilePath))
            {
                Directory.CreateDirectory(FilePath);
            }
            //将文件上传到服务器
            HttpPostedFile UserHPF = FileUpload1.PostedFile;

            try
            {
                string fileContentType = UserHPF.ContentType;// 获取客户端发送的文件的 MIME 内容类型
                if (fileContentType == "application/vnd.ms-excel")
                {
                    if (UserHPF.ContentLength > 0)
                    {
                        UserHPF.SaveAs(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName));//将上传的文件存放在指定的文件夹中
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('文件类型不符合要求,请您核对后重新上传!');", true);
                    ModalPopupExtenderSearch.Hide();
                    return;
                }
            }
            catch
            {
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('文件上传过程中出现错误!');", true);
                ModalPopupExtenderSearch.Hide();
                return;
            }

            using (FileStream fs = File.OpenRead(FilePath + "//" + System.IO.Path.GetFileName(UserHPF.FileName)))
            {
                //根据文件流创建一个workbook
                IWorkbook wk = new HSSFWorkbook(fs);

                ISheet sheet1 = wk.GetSheetAt(0);
                if (tb_Time.Text.ToString().Trim() != "")
                {
                    for (int i = 1; i < sheet1.LastRowNum + 1; i++)
                    {
                        string sh_stid   = "";
                        string stdepid   = "";
                        string strcell01 = "";
                        IRow   row       = sheet1.GetRow(i);
                        ICell  cell01    = row.GetCell(1);
                        try
                        {
                            strcell01 = cell01.ToString().Trim();
                        }
                        catch
                        {
                            strcell01 = "";
                        }
                        if (strcell01 != "")
                        {
                            ICell  cell1    = row.GetCell(1);
                            string strcell1 = cell1.ToString().Trim();
                            string sqltext  = "select ST_ID,ST_DEPID from TBDS_STAFFINFO where ST_WORKNO='" + strcell1 + "'";
                            System.Data.DataTable dttext = DBCallCommon.GetDTUsingSqlText(sqltext);
                            if (dttext.Rows.Count > 0)
                            {
                                ICell  cell3    = row.GetCell(3);
                                ICell  cell4    = row.GetCell(4);
                                string strcell3 = "";
                                string strcell4 = "";
                                try
                                {
                                    strcell3 = cell3.NumericCellValue.ToString().Trim();
                                    strcell4 = cell4.NumericCellValue.ToString().Trim();
                                }
                                catch
                                {
                                    try
                                    {
                                        strcell3 = cell3.ToString().Trim();
                                        strcell4 = cell4.ToString().Trim();
                                    }
                                    catch
                                    {
                                        strcell3 = "";
                                        strcell4 = "";
                                    }
                                }
                                string sqlupdate = "";
                                sh_stid = dttext.Rows[0]["ST_ID"].ToString().Trim();
                                stdepid = dttext.Rows[0]["ST_DEPID"].ToString().Trim();
                                if (stdepid == "13" || stdepid == "08" || stdepid == "09")
                                {
                                    sqlupdate = "update TBDS_KaoHeTotal set Kh_ScoreHP='" + CommonFun.ComTryDecimal(strcell3) + "',Kh_ScoreLD='" + CommonFun.ComTryDecimal(strcell4) + "' where Kh_Id='" + sh_stid + "' and Kh_Year='" + tb_Time.Text.ToString().Trim().Substring(0, 4) + "' and Kh_Month='" + tb_Time.Text.ToString().Trim().Substring(5, 2) + "'";
                                }
                                else
                                {
                                    sqlupdate = "update TBDS_KaoHeTotal set Kh_ScoreHP='" + CommonFun.ComTryDecimal(strcell3) + "' where Kh_Id='" + sh_stid + "' and Kh_Year='" + tb_Time.Text.ToString().Trim().Substring(0, 4) + "' and Kh_Month='" + tb_Time.Text.ToString().Trim().Substring(5, 2) + "'";
                                }
                                list.Add(sqlupdate);
                            }
                        }
                        else
                        {
                            break;
                        }
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "alert('请选择年月!!!');", true);
                    ModalPopupExtenderSearch.Hide();
                    return;
                }
            }
            DBCallCommon.ExecuteTrans(list);
            updatetotalscore();                                       //更新总分数
            foreach (string fileName in Directory.GetFiles(FilePath)) //清空该文件夹下的文件
            {
                string newName = fileName.Substring(fileName.LastIndexOf("\\") + 1);
                System.IO.File.Delete(FilePath + "\\" + newName);//删除文件下储存的文件
            }
            ModalPopupExtenderSearch.Hide();
            UCPaging1.CurrentPage = 1;
            this.bindGrid();
        }
コード例 #8
0
        private void savePf_MenuItem_Click(object sender, RoutedEventArgs e)
        {
            if (docPfdataGrid.ItemsSource == null)
            {
                return;
            }
            HSSFWorkbook wk;
            string       z_name   = "";
            string       z_pfdate = "";

            using (FileStream f = new FileStream(@"temp1.xls", FileMode.OpenOrCreate))
            {
                wk = new HSSFWorkbook(f);
                ISheet    sh     = wk.GetSheet("Sheet1");
                List <PF> x      = (List <PF>)docPfdataGrid.ItemsSource;
                var       zn     = (from g in x select g.站名).First();
                var       pftime = (from dd in x select dd.派发日期).First();
                z_name   = zn;
                z_pfdate = pftime.HasValue == true?pftime.Value.ToString("yyyy年M月dd") : "unknown";

                IRow   r    = sh.GetRow(1);
                ICell  c    = r.GetCell(1);
                string note =
                    string
                    .Format("   {0},经对{1}站结合部设备问题收集,存在下列问题,计划下周整治,请按计划时间准备人力、料具到现场共同配合",
                            pftime.Value.ToString("yyyy年M月d日"), zn);
                c.SetCellValue(note);
                // 序号(3,0),设备名称(3,1),存在问题(3,2),整治方案(3,6),计划时间(3,7),整治情况(3.8),负责人(3.9).
                for (int i = 0; i < x.Count(); i++)
                {
                    IRow row = sh.GetRow(3 + i);
                    row.GetCell(0).SetCellValue(i + 1);
                    row.GetCell(1).SetCellValue(x[i].设备名称);
                    row.GetCell(2).SetCellValue(x[i].存在问题);
                    row.GetCell(6).SetCellValue(x[i].保留2);
                    row.GetCell(7).SetCellValue(x[i].计划时间.Value);
                }
            }
            string   tmpfn = System.IO.Path.GetTempFileName();
            string   fn    = System.IO.Path.ChangeExtension(tmpfn, "xls");
            FileInfo finfo = new FileInfo(tmpfn);

            finfo.MoveTo(fn);

            TempFile.Add(fn);
            using (FileStream s = new FileStream(fn, FileMode.OpenOrCreate))
            {
                wk.Write(s);
            }
            string curDir = System.Environment.CurrentDirectory;

            string saveName = string.Format(@"{0}/user/{1}站{2}整治表.xls", curDir, z_name, z_pfdate);

            using (FileStream savefn = new FileStream(saveName, FileMode.OpenOrCreate))
            {
                wk.Write(savefn);
            }
            Process.Start(fn);
            //string fn = g_var.getSaveDialog();
            //if (fn==null)
            //{
            //    return;
            //}
            //
            //

            //XElement AllDoc = new XElement("记录集");
            //XElement doc = new XElement("re", "re");
            //foreach (var it in x)
            //{
            //    string dt1 = string.Empty;
            //    if (it.计划时间!=null)
            //    {
            //        dt1 = it.计划时间.Value.ToString("yyyy年M月d日");
            //    }
            //    doc =
            //       new XElement("Record",
            //               new XElement("设备名称", it.设备名称),
            //               new XElement("存在问题", it.存在问题),
            //               new XElement("整改方案", it.保留2),
            //               new XElement("计划时间", dt1),
            //               new XElement("整治情况"  ),
            //               new XElement("整治负责人")
            //               );
            //    AllDoc.Add(doc);

            //}
            //AllDoc.Save(fn);
        }
コード例 #9
0
        /**
         * Loops over all cells in all sheets of the supplied
         *  workbook.
         * For cells that contain formulas, their formulas are
         *  Evaluated, and the results are saved. These cells
         *  remain as formula cells.
         * For cells that do not contain formulas, no Changes
         *  are made.
         * This is a helpful wrapper around looping over all
         *  cells, and calling EvaluateFormulaCell on each one.
         */

        public static void EvaluateAllFormulaCells(HSSFWorkbook wb)
        {
            EvaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb));
        }
コード例 #10
0
ファイル: ToExcel.cs プロジェクト: heartFireTree/Export
        /// <summary>
        /// 分表导出到多个Sheet
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="hidText"></param>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public static MemoryStream ExportMultipleSheet(List <DataTable> dtSource, List <string> strHeaderText, List <string> hidText, string FileName = null)
        {
            IWorkbook workbook = null;

            if (!string.IsNullOrWhiteSpace(FileName))
            {
                if (FileName.IndexOf(".xlsx") > 0)
                {
                    workbook = new XSSFWorkbook();
                }
                // 2003版本
                else if (FileName.IndexOf(".xls") > 0)
                {
                    workbook = new HSSFWorkbook();
                }
            }
            else
            {
                workbook = new HSSFWorkbook();
            }

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

                dsi.Company = CompanyName;

                //workbook.DocumentSummaryInformation = dsi;

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

            //表头样式
            ICellStyle headStyleOne = workbook.CreateCellStyle();
            headStyleOne.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 18;
            font.Boldweight         = 700;
            headStyleOne.SetFont(font);
            //表头1
            ICellStyle headStyleTwo = workbook.CreateCellStyle();
            headStyleTwo.Alignment = HorizontalAlignment.Right;
            headStyleTwo.Indention = 50;

            //列头样式
            ICellStyle headStyleThree = workbook.CreateCellStyle();
            headStyleThree.Alignment         = HorizontalAlignment.Left;
            headStyleThree.VerticalAlignment = VerticalAlignment.Center;
            IFont fonts = workbook.CreateFont();
            fonts.FontHeightInPoints = 10;
            fonts.Boldweight         = 700;
            headStyleThree.SetFont(fonts);

            //每个表头名
            var HeadText = string.Empty;
            //每个表头二
            var HeadTextTwo = string.Empty;
            //遍历多张表(每一张表创建一个Sheet)
            for (int i = 0; i < dtSource.Count; i++)
            {
                HeadText    = strHeaderText[i];
                HeadTextTwo = hidText[i];
                #region 处理cell样式和大小
                ICellStyle dateStyle = workbook.CreateCellStyle();

                IDataFormat format = workbook.CreateDataFormat();

                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                //取得列宽
                int[] arrColWidth = new int[dtSource[i].Columns.Count];
                foreach (DataColumn item in dtSource[i].Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int r = 0; r < dtSource[i].Rows.Count; r++)
                {
                    for (int j = 0; j < dtSource[i].Columns.Count; j++)
                    {
                        var intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[i].Rows[r][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                ICellStyle tdStyle = null;

                if (!string.IsNullOrWhiteSpace(FileName))
                {
                    //2007版本
                    if (FileName.IndexOf(".xlsx") > 0)
                    {
                        tdStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                    }
                    // 2003版本
                    else if (FileName.IndexOf(".xls") > 0)
                    {
                        tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    }
                }
                else
                {
                    tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                }
                #endregion

                int rowIndex = 0;

                ISheet sheet = workbook.CreateSheet();

                foreach (DataRow row in dtSource[i].Rows)
                {
                    #region 新建表,填充表头,填充列头,样式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }
                        #region 表头及样式
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 30;
                            headerRow.CreateCell(0).SetCellValue(HeadText);
                            headerRow.GetCell(0).CellStyle = headStyleOne;
                            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[i].Columns.Count - 1));
                        }
                        #endregion
                        {
                            IRow headerRow = sheet.CreateRow(1);

                            headerRow.CreateCell(0).SetCellValue(HeadTextTwo + "| 导出时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
                            headerRow.HeightInPoints = 18;

                            headerRow.GetCell(0).CellStyle = headStyleTwo;
                            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtSource[i].Columns.Count - 1));
                        }
                        #region 列头及样式
                        {
                            IRow headerRow = sheet.CreateRow(2);
                            headerRow.HeightInPoints = 18;

                            foreach (DataColumn column in dtSource[i].Columns)
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                headerRow.GetCell(column.Ordinal).CellStyle = headStyleThree;
                                //设置列宽
                                if (column.ColumnName == "款号图片")
                                {
                                    sheet.SetColumnWidth(column.Ordinal, 20 * 256);
                                }
                                else
                                {
                                    var columnWidth = (arrColWidth[column.Ordinal] + 1) * 256;
                                    sheet.SetColumnWidth(column.Ordinal, columnWidth > (255 * 256) ? 6000 : columnWidth);
                                    //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                                }
                            }
                        }
                        #endregion
                        rowIndex = 3;
                    }
                    #endregion

                    #region 填充内容
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.HeightInPoints = 18;
                    //if (rowIndex % 2 == 0)
                    //    tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                    //else
                    //    tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
                    //tdStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
                    //tdStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
                    ////设置单元格边框
                    //tdStyle.BorderBottom = BorderStyle.THIN;
                    //tdStyle.BorderLeft = BorderStyle.THIN;
                    //tdStyle.BorderRight = BorderStyle.THIN;
                    //tdStyle.BorderTop = BorderStyle.THIN;
                    tdStyle.VerticalAlignment = VerticalAlignment.Center;
                    short datet  = workbook.CreateDataFormat().GetFormat("yyyy-mm-dd");
                    bool  isCell = dtSource[i].Rows.Count < 4000; //4000行bug
                    InitRowData(dtSource[i], tdStyle, row, dataRow, isCell, workbook, rowIndex, HeadText);
                    #endregion
                    rowIndex++;
                }
            }

            ExcelStream stream = new ExcelStream();
            workbook.Write(stream);
            stream.CanDispose = true;

            return(stream);
        }
コード例 #11
0
    static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
    {
        foreach (string asset in importedAssets)
        {
            if (!filePath.Equals(asset))
            {
                continue;
            }

            Entity_Job data = (Entity_Job)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_Job));
            if (data == null)
            {
                data = ScriptableObject.CreateInstance <Entity_Job> ();
                AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
                data.hideFlags = HideFlags.NotEditable;
            }

            data.sheets.Clear();
            using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) {
                IWorkbook book = new HSSFWorkbook(stream);

                foreach (string sheetName in sheetNames)
                {
                    ISheet sheet = book.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        Debug.LogError("[QuestData] sheet not found:" + sheetName);
                        continue;
                    }

                    Entity_Job.Sheet s = new Entity_Job.Sheet();
                    s.name = sheetName;

                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow  row  = sheet.GetRow(i);
                        ICell cell = null;

                        Entity_Job.Param p = new Entity_Job.Param();

                        cell = row.GetCell(0); p.id = (int)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(1); p.name = (cell == null ? "" : cell.StringCellValue);
                        cell = row.GetCell(2); p.lv = (int)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(3); p.difficult = (int)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(4); p.attack = (float)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(5); p.defens = (float)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(6); p.sp = (int)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(7); p.cooltime = (float)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(8); p.casttime = (float)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(9); p.effect = (float)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(10); p.point = (int)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(11); p.bonus = (float)(cell == null ? 0 : cell.NumericCellValue);
                        cell = row.GetCell(12); p.type = (int)(cell == null ? 0 : cell.NumericCellValue);
                        s.list.Add(p);
                    }
                    data.sheets.Add(s);
                }
            }

            ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
            EditorUtility.SetDirty(obj);
        }
    }
コード例 #12
0
ファイル: ExcelHelper.cs プロジェクト: bryanNET/AutoUpSvn
        /// <summary>
        /// 更新excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public bool ReadFromExcelFile(string filePath)
        {
            bool      b         = false;
            int       startRow  = 3;
            int       startCell = 6;
            int       endCell   = 3;
            IWorkbook wk        = null;

            string extension = System.IO.Path.GetExtension(filePath);

            try
            {
                using (var fs = File.OpenRead(filePath))
                {
                    //FileStream fs = File.Open(filePath,FileMode.OpenOrCreate, FileAccess.ReadWrite))
                    //FileStream fs = File.OpenRead(filePath);  OpenWrite
                    if (extension != null && extension.Equals(".xls"))
                    {
                        //把xls文件中的数据写入wk中
                        wk = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        //把xlsx文件中的数据写入wk中
                        wk = new XSSFWorkbook(fs);
                    }
                    fs.Close();

                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(0);
                    sheet.ForceFormulaRecalculation = true;
                    for (int i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i); //读取当前行数据
                        if (row != null)
                        {
                            //项目
                            if (row.GetCell(0) == null || string.IsNullOrWhiteSpace(row.GetCell(0).ToString()) || row.GetCell(0).ToString().ToUpper().Contains("PM") == false)
                            {
                                continue;
                            }
                            Console.Write(i + "行:");

                            var gs = row.GetCell(14);
                            if (gs == null)
                            {
                                Console.Write(" O列的公式 =ROUND(100/项目总天数,0) 没有值,报表格式异常跳过 ");
                                return(b);
                            }
                            if (gs.NumericCellValue == 0)
                            {
                                Console.Write(gs.NumericCellValue + " O列的公式没有值,报表格式异常跳过 ");
                                return(b);
                            }
                            //工序计划交期
                            DateTime overdt = DateTime.MaxValue;
                            string   overDT = row.GetCell(5).ToString();
                            if (string.IsNullOrWhiteSpace(overDT))
                            {
                                Console.Write("工序计划交期 没有值,报表格式异常跳过 ");
                                return(b);
                            }
                            else
                            {
                                overdt = Convert.ToDateTime(overDT);
                            }
                            int len = startCell + endCell;
                            //LastCellNum 是当前行的总列数row.LastCellNum
                            for (int j = startCell; j < len; j++)
                            {
                                Console.Write(j + "列=  ");
                                //读取该行的第j列数据
                                if (row.GetCell(j + 1) != null && row.GetCell(j + 1).NumericCellValue > 0)
                                {
                                    double value = row.GetCell(j).NumericCellValue;
                                    Console.Write(value + "改为");

                                    double d = 0;
                                    if (j == 8)
                                    {
                                        //=IF((I5*100-O5)/100<0,0,((I5*100-O5)/100))
                                        d = (row.GetCell(j).NumericCellValue * 100 - gs.NumericCellValue) / 100;
                                    }
                                    else
                                    {
                                        d = row.GetCell(j + 1).NumericCellValue;
                                    }
                                    Console.Write(d + " |   ");


                                    row.GetCell(j).SetCellValue(d > 0 ? d : 0);
                                }
                                else
                                {
                                    row.GetCell(j).SetCellValue(0);
                                    Console.Write("0 |   ");
                                }
                            }

                            if (row.GetCell(6).NumericCellValue <= 0)
                            {
                                if (overdt < DateTime.Now)
                                {
                                    row.GetCell(10).SetCellValue("测试中");
                                }
                                //else
                                //    row.GetCell(10).SetCellValue("已完成");
                            }
                            Console.WriteLine("\n");
                        }
                    }

                    b = true;
                    //模板自动公式需要激活
                    sheet.ForceFormulaRecalculation = true;

                    try
                    {
                        //转为字节数组
                        var stream = new MemoryStream();
                        wk.Write(stream);
                        var buf = stream.ToArray();
                        //保存为Excel文件
                        using (FileStream fs5 = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                        {
                            fs5.Write(buf, 0, buf.Length);
                            fs5.Flush();
                        }
                        Console.Write("保存OK=" + filePath);
                    }
                    catch (Exception e)
                    {
                        b = false;
                        Logs.WriteLog(e);
                    }
                }
            }
            catch (Exception e)
            {
                b = false;
                Logs.WriteLog(e);
            }
            return(b);
        }
コード例 #13
0
        public JsonResult ExportAdd(EditViewModel model)
        {
            CustomJsonResult r = new CustomJsonResult();

            r.ContentType = "text/html";

            try
            {
                HttpPostedFileBase file_upload = Request.Files[0];

                if (file_upload == null)
                {
                    return(Json("text/html", ResultType.Failure, "找不到上传的对象"));
                }

                if (file_upload.ContentLength == 0)
                {
                    return(Json("text/html", ResultType.Failure, "文件内容为空,请重新选择"));
                }

                System.IO.FileInfo file = new System.IO.FileInfo(file_upload.FileName);
                string             ext  = file.Extension.ToLower();

                if (ext != ".xls")
                {
                    return(Json("text/html", ResultType.Failure, "上传的文件不是excel格式(xls)"));
                }

                HSSFWorkbook workbook = new HSSFWorkbook(file_upload.InputStream);

                ISheet sheet = workbook.GetSheetAt(0);

                ExportCheckErrorPoint exportCheckErrorPoint = new ExportCheckErrorPoint();

                IRow rowTitle = sheet.GetRow(0);

                exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(0), "设备ID");
                exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(1), "机身号");
                exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(2), "终端号");
                exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(3), "版本号");
                exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(4), "押金");
                exportCheckErrorPoint.CheckCellTitle(rowTitle.GetCell(5), "租金");


                if (exportCheckErrorPoint.TitleHasError)
                {
                    return(Json("text/html", ResultType.Failure, "上传的文件模板错误,请点击下载的文件模板"));
                }


                int rowCount = sheet.LastRowNum + 1;

                if (rowCount == 1)
                {
                    return(Json("text/html", ResultType.Failure, "上传的文件数据为空,请检查后再次上传"));
                }


                List <PosMachine> posMachines = new List <PosMachine>();
                for (int i = 1; i < rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);

                    string deviceId       = row.GetCell(0).ToString();
                    string fuselageNumber = row.GetCell(1) == null ? "" : row.GetCell(1).ToString();
                    string terminalNumber = row.GetCell(2) == null ? "" : row.GetCell(2).ToString();
                    string version        = row.GetCell(3) == null ? "" : row.GetCell(3).ToString();
                    string deposit        = row.GetCell(4) == null ? "" : row.GetCell(4).ToString();
                    string rent           = row.GetCell(5) == null ? "" : row.GetCell(5).ToString();

                    if (string.IsNullOrEmpty(deviceId))
                    {
                        return(Json("text/html", ResultType.Failure, "检查到有为空的设备ID,请完善后再次上传"));
                    }

                    if (string.IsNullOrEmpty(deviceId) || deviceId.Length > 100)
                    {
                        exportCheckErrorPoint.AddPoint(deviceId, "设备ID不能为空,且不能超过100个字符");
                    }

                    if (string.IsNullOrEmpty(fuselageNumber) || fuselageNumber.Length > 100)
                    {
                        exportCheckErrorPoint.AddPoint(deviceId, "机身号不能为空,且不能超过100个字符");
                    }

                    if (string.IsNullOrEmpty(terminalNumber) || terminalNumber.Length > 100)
                    {
                        exportCheckErrorPoint.AddPoint(deviceId, "终端号不能为空,且不能超过100个字符");
                    }

                    if (string.IsNullOrEmpty(version) || version.Length > 100)
                    {
                        exportCheckErrorPoint.AddPoint(deviceId, "版本号不能为空,且不能超过100个字符");
                    }

                    if (CommonUtils.IsDecimal(deposit))
                    {
                        exportCheckErrorPoint.AddPoint(deviceId, "押金必须数字格式,且整数位最多16位,小数位最多2位");
                    }

                    if (CommonUtils.IsDecimal(rent))
                    {
                        exportCheckErrorPoint.AddPoint(deviceId, "租金必须数字格式,且整数位最多16位,小数位最多2位");
                    }

                    PosMachine posMachine = new PosMachine();
                    posMachine.DeviceId       = deviceId;
                    posMachine.FuselageNumber = fuselageNumber;
                    posMachine.TerminalNumber = terminalNumber;
                    posMachine.Version        = version;
                    posMachines.Add(posMachine);
                }


                if (exportCheckErrorPoint.ErrorPoint.Count > 0)
                {
                    return(Json("text/html", ResultType.Failure, exportCheckErrorPoint.ErrorPoint, "更新数据失败,检查到无效的数据"));
                }


                using (TransactionScope ts = new TransactionScope())
                {
                    foreach (var posMachine in posMachines)
                    {
                        var old = CurrentDb.PosMachine.Where(m => m.DeviceId == posMachine.DeviceId).FirstOrDefault();
                        if (old != null)
                        {
                            exportCheckErrorPoint.AddPoint(old.DeviceId, "设备ID号:" + posMachine.DeviceId + ",已经存在");
                        }
                    }


                    if (exportCheckErrorPoint.ErrorPoint.Count > 0)
                    {
                        return(Json("text/html", ResultType.Failure, exportCheckErrorPoint.ErrorPoint, "更新数据失败,检查到无效的数据"));
                    }

                    DateTime dateNow = DateTime.Now;
                    foreach (var posMachine in posMachines)
                    {
                        posMachine.CreateTime = dateNow;
                        posMachine.Creator    = this.CurrentUserId;
                        CurrentDb.PosMachine.Add(posMachine);
                    }

                    CurrentDb.SaveChanges();
                    ts.Complete();
                }


                return(Json("text/html", ResultType.Success, "上传成功"));
            }
            catch (Exception ex)
            {
                Log.Error("导入POS机信息", ex);
                return(Json("text/html", ResultType.Exception, "上传失败,系统出现异常"));
            }
        }
コード例 #14
0
        /// <summary>
        /// 读取excel
        /// sheet.LastRowNum属性获取的是Excel中该工作表(sheet)的末行行号减1;
        /// headerRow.LastCellNum属性获取的是Excel中该行的列数
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="sheetNum">工作表索引,以0开始</param>
        /// <param name="startRowNum">起始行号,即:表头在Excel中的行号</param>
        /// <param name="endRowNum">结束行号</param>
        /// <param name="startColNum">起始列号</param>
        /// <param name="endColNum">结束列号</param>
        /// <returns>DataTable</returns>
        public DataTable Import(string strFileName, int sheetNum, int startRowNum, int endRowNum, int startColNum, int endColNum)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }

            int sheetCount = hssfworkbook.NumberOfSheets;

            sheetNum = sheetNum < 0 || sheetNum > sheetCount - 1 ? 0 : sheetNum;

            HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum);

            HSSFRow headerRow = null;

            #region 行列号范围验证

            startColNum = startColNum < 0 ? 0 : startColNum;

            startRowNum = startRowNum < 1 ? 1 : startRowNum;

            headerRow = (HSSFRow)sheet.GetRow(startRowNum - 1);

            endColNum = (endColNum > headerRow.LastCellNum || endColNum < 1) ? headerRow.LastCellNum : endColNum;

            endRowNum = (endRowNum - 1 > sheet.LastRowNum || endRowNum < 0) ? sheet.LastRowNum + 1 : endColNum;

            #endregion

            //添加列
            for (int j = startColNum - 1; j < endColNum; j++)
            {
                HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            //添加行
            for (int i = startRowNum; i <= endRowNum - 1; i++)
            {
                HSSFRow row = (HSSFRow)sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = startColNum - 1; j < endColNum; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j - startColNum + 1] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }
コード例 #15
0
ファイル: TestDrawingShapes.cs プロジェクト: flowbywind/npoi
        /**
         * create a rectangle, save the workbook, read back and verify that all shape properties are there
         */
        public void TestReadWriteRectangle()
        {
            HSSFWorkbook wb    = new HSSFWorkbook();
            HSSFSheet    sheet = wb.CreateSheet() as HSSFSheet;

            HSSFPatriarch    drawing = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFClientAnchor anchor  = new HSSFClientAnchor(10, 10, 50, 50, (short)2, 2, (short)4, 4);

            anchor.AnchorType = (AnchorType)(2);
            Assert.AreEqual(anchor.AnchorType, 2);

            HSSFSimpleShape rectangle = drawing.CreateSimpleShape(anchor);

            rectangle.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
            rectangle.LineWidth = (10000);
            rectangle.FillColor = (777);
            Assert.AreEqual(rectangle.FillColor, 777);
            Assert.AreEqual(10000, rectangle.LineWidth);
            rectangle.LineStyle = (LineStyle)(10);
            Assert.AreEqual(10, rectangle.LineStyle);
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_SQUARE);
            rectangle.LineStyleColor = (1111);
            rectangle.IsNoFill       = (true);
            rectangle.WrapText       = (HSSFSimpleShape.WRAP_NONE);
            rectangle.String         = (new HSSFRichTextString("teeeest"));
            Assert.AreEqual(rectangle.LineStyleColor, 1111);
            Assert.AreEqual(((EscherSimpleProperty)((EscherOptRecord)HSSFTestHelper.GetEscherContainer(rectangle).GetChildById(EscherOptRecord.RECORD_ID))
                             .Lookup(EscherProperties.TEXT__TEXTID)).PropertyValue, "teeeest".GetHashCode());
            Assert.AreEqual(rectangle.IsNoFill, true);
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);
            Assert.AreEqual(rectangle.String.String, "teeeest");

            wb      = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet   = wb.GetSheetAt(0) as HSSFSheet;
            drawing = sheet.DrawingPatriarch as HSSFPatriarch;
            Assert.AreEqual(1, drawing.Children.Count);

            HSSFSimpleShape rectangle2 =
                (HSSFSimpleShape)drawing.Children[0];

            Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE,
                            rectangle2.ShapeType);
            Assert.AreEqual(10000, rectangle2.LineWidth);
            Assert.AreEqual(10, (int)rectangle2.LineStyle);
            Assert.AreEqual(anchor, rectangle2.Anchor);
            Assert.AreEqual(rectangle2.LineStyleColor, 1111);
            Assert.AreEqual(rectangle2.FillColor, 777);
            Assert.AreEqual(rectangle2.IsNoFill, true);
            Assert.AreEqual(rectangle2.String.String, "teeeest");
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);

            rectangle2.FillColor      = (3333);
            rectangle2.LineStyle      = (LineStyle)(9);
            rectangle2.LineStyleColor = (4444);
            rectangle2.IsNoFill       = (false);
            rectangle2.LineWidth      = (77);
            rectangle2.Anchor.Dx1     = 2;
            rectangle2.Anchor.Dx2     = 3;
            rectangle2.Anchor.Dy1     = (4);
            rectangle2.Anchor.Dy2     = (5);
            rectangle.WrapText        = (HSSFSimpleShape.WRAP_BY_POINTS);
            rectangle2.String         = (new HSSFRichTextString("test22"));

            wb      = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet   = wb.GetSheetAt(0) as HSSFSheet;
            drawing = sheet.DrawingPatriarch as HSSFPatriarch;
            Assert.AreEqual(1, drawing.Children.Count);
            rectangle2 = (HSSFSimpleShape)drawing.Children[0];
            Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.ShapeType);
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_BY_POINTS);
            Assert.AreEqual(77, rectangle2.LineWidth);
            Assert.AreEqual(9, rectangle2.LineStyle);
            Assert.AreEqual(rectangle2.LineStyleColor, 4444);
            Assert.AreEqual(rectangle2.FillColor, 3333);
            Assert.AreEqual(rectangle2.Anchor.Dx1, 2);
            Assert.AreEqual(rectangle2.Anchor.Dx2, 3);
            Assert.AreEqual(rectangle2.Anchor.Dy1, 4);
            Assert.AreEqual(rectangle2.Anchor.Dy2, 5);
            Assert.AreEqual(rectangle2.IsNoFill, false);
            Assert.AreEqual(rectangle2.String.String, "test22");

            HSSFSimpleShape rect3 = drawing.CreateSimpleShape(new HSSFClientAnchor());

            rect3.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            drawing = (wb.GetSheetAt(0) as HSSFSheet).DrawingPatriarch as HSSFPatriarch;
            Assert.AreEqual(drawing.Children.Count, 2);
        }
コード例 #16
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="headerTextList">表头摘要信息</param>
        public MemoryStream Export(DataTable dtSource, List<String> headerTextList)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");

            //设置Excel文件属性信息
            SetFileProperty(workbook);

            HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFDataFormat format = (HSSFDataFormat)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 表头及样式
                    for (int i = 0; i < headerTextList.Count; i++)
                    {
                        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);
                        headerRow.HeightInPoints = 18;
                        headerRow.CreateCell(0).SetCellValue(headerTextList[i]);

                        HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        headerStyle.Alignment = HorizontalAlignment.Left;
                        HSSFFont font = (HSSFFont)workbook.CreateFont();
                        font.FontHeightInPoints = 14;
                        //font.Boldweight = 700;
                        headerStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headerStyle;
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count);
                        HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        HSSFFont font = (HSSFFont)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 = headerTextList.Count + 1;
                }

                #endregion

                #region 填充表格内容

                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = (HSSFCell)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;

                return ms;
            }

        }
コード例 #17
0
ファイル: TestDrawingShapes.cs プロジェクト: flowbywind/npoi
        public void TestRemoveShapes()
        {
            HSSFWorkbook  wb        = new HSSFWorkbook();
            HSSFSheet     sheet     = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;

            HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor());

            rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;

            int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG);

            patriarch.CreatePicture(new HSSFClientAnchor(), idx);

            patriarch.CreateCellComment(new HSSFClientAnchor());

            HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());

            polygon.SetPoints(new int[] { 1, 2 }, new int[] { 2, 3 });

            patriarch.CreateTextbox(new HSSFClientAnchor());

            HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());

            group.CreateTextbox(new HSSFChildAnchor());
            group.CreatePicture(new HSSFChildAnchor(), idx);

            Assert.AreEqual(patriarch.Children.Count, 6);
            Assert.AreEqual(group.Children.Count, 2);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            Assert.AreEqual(patriarch.Children.Count, 6);

            group = (HSSFShapeGroup)patriarch.Children[5];
            group.RemoveShape(group.Children[0]);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            group = (HSSFShapeGroup)patriarch.Children[(5)];
            patriarch.RemoveShape(group);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 5);

            HSSFShape shape = patriarch.Children[0];

            patriarch.RemoveShape(shape);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 4);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 4);

            HSSFPicture picture = (HSSFPicture)patriarch.Children[0];

            patriarch.RemoveShape(picture);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 3);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 3);

            HSSFComment comment = (HSSFComment)patriarch.Children[0];

            patriarch.RemoveShape(comment);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
            Assert.AreEqual(patriarch.Children.Count, 2);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
            Assert.AreEqual(patriarch.Children.Count, 2);

            polygon = (HSSFPolygon)patriarch.Children[0];
            patriarch.RemoveShape(polygon);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
            Assert.AreEqual(patriarch.Children.Count, 1);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
            Assert.AreEqual(patriarch.Children.Count, 1);

            HSSFTextbox textbox = (HSSFTextbox)patriarch.Children[0];

            patriarch.RemoveShape(textbox);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
            Assert.AreEqual(patriarch.Children.Count, 0);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet     = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
            Assert.AreEqual(patriarch.Children.Count, 0);
        }
コード例 #18
0
        /// <summary>
        /// 导出加工工艺表
        /// </summary>
        /// <param name="partIds"></param>
        /// <returns></returns>
        public virtual async Task <string> ExportPartProcessInfo(int[] partIds)
        {
            var parts = await Manager.GetAll()
                        .Include(o => o.Project).ThenInclude(o => o.Unit)
                        .Include("ProcessTasks.ProcessType")
                        .Where(o => partIds.Contains(o.Id))
                        .Select(o => new {
                o.Project.ProjectSN,
                o.Project.ProjectName,
                ProjectCharger = o.Project.GetPropertyValue <string>("ProjectCharger"),
                UnitName       = o.Project.Unit != null?o.Project.Unit.UnitName:"",
                o.PartName,
                o.PartImg,
                o.PartSpecification,
                Tasks = o.ProcessTasks.Select(t => new {
                    t.ProcessType.ProcessTypeName,
                    t.AppointDate,
                    t.RequireDate,
                    t.StartDate,
                    t.EndDate
                })
            })
                        .ToListAsync();

            #region 导出方法
            IWorkbook workbook = new HSSFWorkbook();
            ISheet    sheet    = workbook.CreateSheet("模具制作工艺单");
            sheet.DefaultColumnWidth = 300 * 256;//貌似没有作用
            var rowIndex  = 0;
            var cellIndex = 0;
            //模具信息行
            IRow row = sheet.CreateRow(rowIndex++);
            parts.ForEach(o => {
                row.CreateCell(cellIndex++).SetCellValue(o.ProjectSN);
                row.CreateCell(cellIndex++).SetCellValue(o.ProjectName);
                row.CreateCell(cellIndex++).SetCellValue(o.ProjectCharger);
                row.CreateCell(cellIndex++).SetCellValue(o.UnitName);
            });
            //零件信息行
            row       = sheet.CreateRow(rowIndex++);
            cellIndex = 0;
            parts.ForEach(o => {
                row.CreateCell(cellIndex++).SetCellValue(o.PartName);
                row.CreateCell(cellIndex++).SetCellValue(o.PartSpecification);
                row.CreateCell(cellIndex++).SetCellValue("");
                row.CreateCell(cellIndex++).SetCellValue("");
            });
            //图片行
            row        = sheet.CreateRow(rowIndex++);
            row.Height = 80 * 20;
            cellIndex  = 0;
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            parts.ForEach(o => {
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex - 1, cellIndex, cellIndex + 3));
                if (!string.IsNullOrEmpty(o.PartImg))
                {
                    try
                    {
                        byte[] bytes   = System.IO.File.ReadAllBytes(Common.PathHelper.VirtualPathToAbsolutePath(o.PartImg));
                        int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
                        // 插图片的位置  HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
                        HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cellIndex, rowIndex - 1, cellIndex + 4, rowIndex);
                        //把图片插到相应的位置
                        HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    }
                    catch (Exception ex)
                    {
                    }
                }

                cellIndex += 4;
            });
            //工序表头
            row       = sheet.CreateRow(rowIndex++);
            cellIndex = 0;
            parts.ForEach(o =>
            {
                row.CreateCell(cellIndex++).SetCellValue("序号");
                row.CreateCell(cellIndex++).SetCellValue("加工流程");
                row.CreateCell(cellIndex++).SetCellValue("计划时间");
                row.CreateCell(cellIndex++).SetCellValue("实际时间");
            });
            //工序内容
            var maxProcessTypeCount = parts.Max(o => o.Tasks.Count());
            for (var i = 0; i < maxProcessTypeCount; i++)
            {
                //先构建下面所有行
                sheet.CreateRow(rowIndex + i);
            }
            cellIndex = 0;
            for (var i = 0; i < parts.Count; i++)
            {
                var part = parts[i];
                for (var j = 0; j < part.Tasks.Count(); j++)
                {
                    var task = part.Tasks.ElementAt(j);
                    row = sheet.GetRow(rowIndex + j);
                    row.CreateCell(i * 4).SetCellValue(j + 1);
                    row.CreateCell(i * 4 + 1).SetCellValue(task.ProcessTypeName);
                    row.CreateCell(i * 4 + 2).SetCellValue(task.AppointDate?.ToString("MM-dd") + "-" + task.RequireDate?.ToString("MM-dd"));
                    row.CreateCell(i * 4 + 3).SetCellValue(task.StartDate?.ToString("MM-dd") + "-" + task.EndDate?.ToString("MM-dd"));
                }
            }
            //边框
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom      = BorderStyle.Thin;
            style.BorderLeft        = BorderStyle.Thin;
            style.BorderRight       = BorderStyle.Thin;
            style.BorderTop         = BorderStyle.Thin;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor   = HSSFColor.Black.Index;
            style.RightBorderColor  = HSSFColor.Black.Index;
            style.TopBorderColor    = HSSFColor.Black.Index;
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                for (int j = 0; j <= row.LastCellNum; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell != null)
                    {
                        cell.CellStyle = style;
                    }
                }
            }
            #endregion
            var filePath = $"/Temp/{Guid.NewGuid()}.xls";
            System.IO.Directory.CreateDirectory(Common.PathHelper.VirtualPathToAbsolutePath("/Temp/"));
            using (var fs = new FileStream(Common.PathHelper.VirtualPathToAbsolutePath(filePath), FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.Write(fs); //写入到excel
            }
            workbook = null;
            return(filePath);
        }
コード例 #19
0
        /// <summary>
        /// Goes through the Wokrbook, optimising the cell styles
        /// by removing duplicate ones.
        /// For best results, optimise the fonts via a call to
        /// OptimiseFonts(HSSFWorkbook) first
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the cell styles</param>
        public static void OptimiseCellStyles(HSSFWorkbook workbook)
        {
            // Where each style has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumExFormats];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each style record, so we can do deletes
            //  without Getting confused
            ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                xfrs[i] = workbook.Workbook.GetExFormatAt(i);
            }

            // Loop over each style, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and
            //  mark the later one as needing deleting
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
                    if (xfCheck.Equals(xfrs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only work on user added ones, which come after 20
            for (int i = 21; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition  = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j])
                    {
                        newPosition--;
                    }
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user style records
            for (int i = 21; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveExFormatRecord(
                        xfrs[i]
                        );
                }
            }

            // Finally, update the cells to point at
            //  their new extended format records
            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                HSSFSheet   s   = (HSSFSheet)workbook.GetSheetAt(sheetNum);
                IEnumerator rIt = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell  = (HSSFCell)cIt.Current;
                        short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
                        Zephyr.Utils.NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
                            newPos[oldXf]
                            );
                        cell.CellStyle = (newStyle);
                    }
                }
            }
        }
コード例 #20
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="dateFormat"></param>
        /// <returns></returns>
        private static MemoryStream DataTableToExcel(DataTable dtSource, string dateFormat)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet    sheet    = (HSSFSheet)workbook.CreateSheet();

            HSSFCellStyle  dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFDataFormat format    = (HSSFDataFormat)workbook.CreateDataFormat();

            dateStyle.DataFormat = format.GetFormat(dateFormat);

            //取得列宽
            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 = (HSSFSheet)workbook.CreateSheet();
                    }

                    #region 列头及样式
                    {
                        HSSFRow       headerRow = (HSSFRow)sheet.CreateRow(0);
                        HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        HSSFFont      font      = (HSSFFont)workbook.CreateFont();
                        headStyle.Alignment         = HorizontalAlignment.Center; //水平居中
                        headStyle.VerticalAlignment = VerticalAlignment.Center;   //垂直居中
                        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 = 1;
                }
                #endregion


                #region 填充内容
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                    newCell.CellStyle.Alignment         = HorizontalAlignment.Center;
                    newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
                    string drValue = row[column].ToString();

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

                    case "System.DateTime":    //日期类型
                        System.DateTime dateV;
                        System.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();
                return(ms);
            }
        }
コード例 #21
0
        /// <summary>
        /// Goes through the Workbook, optimising the fonts by
        /// removing duplicate ones.
        /// For now, only works on fonts used in HSSFCellStyle
        /// and HSSFRichTextString. Any other font uses
        /// (eg charts, pictures) may well end up broken!
        /// This can be a slow operation, especially if you have
        /// lots of cells, cell styles or rich text strings
        /// </summary>
        /// <param name="workbook">The workbook in which to optimise the fonts</param>
        public static void OptimiseFonts(HSSFWorkbook workbook)
        {
            // Where each font has ended up, and if we need to
            //  delete the record for it. Start off with no change
            short[] newPos =
                new short[workbook.Workbook.NumberOfFontRecords + 1];
            bool[] zapRecords = new bool[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                newPos[i]     = (short)i;
                zapRecords[i] = false;
            }

            // Get each font record, so we can do deletes
            //  without Getting confused
            FontRecord[] frecs = new FontRecord[newPos.Length];
            for (int i = 0; i < newPos.Length; i++)
            {
                // There is no 4!
                if (i == 4)
                {
                    continue;
                }

                frecs[i] = workbook.Workbook.GetFontRecordAt(i);
            }

            // Loop over each font, seeing if it is the same
            //  as an earlier one. If it is, point users of the
            //  later duplicate copy to the earlier one, and
            //  mark the later one as needing deleting
            // Note - don't change built in fonts (those before 5)
            for (int i = 5; i < newPos.Length; i++)
            {
                // Check this one for being a duplicate
                //  of an earlier one
                int earlierDuplicate = -1;
                for (int j = 0; j < i && earlierDuplicate == -1; j++)
                {
                    if (j == 4)
                    {
                        continue;
                    }

                    FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
                    if (frCheck.SameProperties(frecs[i]))
                    {
                        earlierDuplicate = j;
                    }
                }

                // If we got a duplicate, mark it as such
                if (earlierDuplicate != -1)
                {
                    newPos[i]     = (short)earlierDuplicate;
                    zapRecords[i] = true;
                }
            }

            // Update the new positions based on
            //  deletes that have occurred between
            //  the start and them
            // Only need to worry about user fonts
            for (int i = 5; i < newPos.Length; i++)
            {
                // Find the number deleted to that
                //  point, and adjust
                short preDeletePos = newPos[i];
                short newPosition  = preDeletePos;
                for (int j = 0; j < preDeletePos; j++)
                {
                    if (zapRecords[j])
                    {
                        newPosition--;
                    }
                }

                // Update the new position
                newPos[i] = newPosition;
            }

            // Zap the un-needed user font records
            for (int i = 5; i < newPos.Length; i++)
            {
                if (zapRecords[i])
                {
                    workbook.Workbook.RemoveFontRecord(
                        frecs[i]
                        );
                }
            }

            // Tell HSSFWorkbook that it needs to
            //  re-start its HSSFFontCache
            workbook.ResetFontCache();

            // Update the cell styles to point at the
            //  new locations of the fonts
            for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
            {
                ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
                xfr.FontIndex = (
                    newPos[xfr.FontIndex]
                    );
            }

            // Update the rich text strings to point at
            //  the new locations of the fonts
            // Remember that one underlying unicode string
            //  may be shared by multiple RichTextStrings!
            ArrayList doneUnicodeStrings = new ArrayList();

            for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
            {
                Zephyr.Utils.NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
                IEnumerator rIt = s.GetRowEnumerator();
                while (rIt.MoveNext())
                {
                    HSSFRow     row = (HSSFRow)rIt.Current;
                    IEnumerator cIt = row.GetEnumerator();
                    while (cIt.MoveNext())
                    {
                        ICell cell = (HSSFCell)cIt.Current;
                        if (cell.CellType == Zephyr.Utils.NPOI.SS.UserModel.CellType.STRING)
                        {
                            HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
                            UnicodeString      u   = rtr.RawUnicodeString;

                            // Have we done this string already?
                            if (!doneUnicodeStrings.Contains(u))
                            {
                                // Update for each new position
                                for (short i = 5; i < newPos.Length; i++)
                                {
                                    if (i != newPos[i])
                                    {
                                        u.SwapFontUse(i, newPos[i]);
                                    }
                                }

                                // Mark as done
                                doneUnicodeStrings.Add(u);
                            }
                        }
                    }
                }
            }
        }
コード例 #22
0
ファイル: CommonHelp.cs プロジェクト: fl1029/QJCore
        public static HSSFWorkbook ExportToExcel(DataTable dt)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            if (dt.Rows.Count > 0)
            {
                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           = HorizontalAlignment.Center;
                HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
                HeadercellStyle.FillPattern         = FillPattern.SolidForeground;
                HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;

                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight         = (short)FontBoldWeight.Bold;
                headerfont.FontHeightInPoints = 12;
                HeadercellStyle.SetFont(headerfont);


                //用column name 作为列名
                int  icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn dc in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(dc.ColumnName);
                    cell.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 = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    int  iCellIndex = 0;
                    IRow irow       = sheet.CreateRow(iRowIndex + 1);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string strsj = string.Empty;
                        if (dr[i] != null)
                        {
                            strsj = dr[i].ToString();
                        }
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(strsj);
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //using (MemoryStream ms = new MemoryStream())
                //{
                //    workbook.Write(ms);

                //    HttpContext curContext = HttpContext.Current;


                //    // 设置编码和附件格式
                //    curContext.Response.ContentType = "application/vnd.ms-excel";
                //    curContext.Response.ContentEncoding = Encoding.UTF8;
                //    curContext.Response.Charset = "";
                //    curContext.Response.AppendHeader("Content-Disposition",
                //        "attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));

                //    curContext.Response.BinaryWrite(ms.GetBuffer());

                //    workbook = null;
                //    ms.Close();
                //    ms.Dispose();

                //    curContext.Response.End();
                //}
            }
            return(workbook);
        }
コード例 #23
0
ファイル: ReportExcel.cs プロジェクト: lexzh/Myproject
 public static void OutputExcel(DataGridView dgv, string Title, string savePath, string sSTName, bool IsConver)
 {
     try
     {
         HSSFWorkbook workbook = new HSSFWorkbook();
         {
             int num  = 5;
             int num2 = 0x106;
             DocumentSummaryInformation information = PropertySetFactory.CreateDocumentSummaryInformation();
             information.Company = "";
             workbook.DocumentSummaryInformation = information;
             SummaryInformation information2 = PropertySetFactory.CreateSummaryInformation();
             information2.Subject        = "";
             workbook.SummaryInformation = information2;
             if (sSTName.Length > 0x1f)
             {
                 sSTName = "报表";
             }
             ISheet     sheet  = workbook.CreateSheet(sSTName);
             int        rownum = 3;
             int        num4   = 0;
             int        num5   = 0;
             int        num6   = 1;
             ICellStyle style  = workbook.CreateCellStyle();
             style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
             style.VerticalAlignment = VerticalAlignment.CENTER;
             style.ShrinkToFit       = true;
             NPOI.SS.UserModel.IFont font = workbook.CreateFont();
             font.FontName           = "宋体";
             font.FontHeightInPoints = 11;
             style.SetFont(font);
             IRow row = sheet.CreateRow(rownum);
             num5 = num4;
             foreach (DataGridViewColumn column in dgv.Columns)
             {
                 if (column.Visible && (column.CellType != typeof(DataGridViewCheckBoxCell)))
                 {
                     num5++;
                     ICell cell = row.CreateCell(num5);
                     cell.SetCellValue(column.HeaderText);
                     cell.CellStyle = style;
                     style.WrapText = true;
                     sheet.AutoSizeColumn(num5);
                 }
             }
             if (IsConver)
             {
                 for (int i = dgv.Rows.Count - 1; i >= 0; i--)
                 {
                     DataGridViewRow row2 = dgv.Rows[i];
                     if (!row2.IsNewRow)
                     {
                         rownum++;
                         num5 = num4;
                         IRow row3 = sheet.CreateRow(rownum);
                         foreach (DataGridViewColumn column2 in dgv.Columns)
                         {
                             if (column2.Visible && (column2.CellType != typeof(DataGridViewCheckBoxCell)))
                             {
                                 num5++;
                                 if ((row2.Cells[column2.Index].Value != null) && !row2.IsNewRow)
                                 {
                                     row3.CreateCell(num5).SetCellValue(row2.Cells[column2.Index].Value.ToString());
                                     int num8 = sheet.GetColumnWidth(row3.GetCell(num5).ColumnIndex);
                                     int num9 = (Encoding.GetEncoding("gb2312").GetBytes(row2.Cells[column2.Index].Value.ToString()).Length + num) * num2;
                                     num8 = (num9 < num8) ? num8 : num9;
                                     sheet.SetColumnWidth(num5, num8);
                                     if (column2.CellType == typeof(DataGridViewTextBoxCell))
                                     {
                                         sheet.GetRow(rownum).GetCell(num5).CellStyle = style;
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
             else
             {
                 foreach (DataGridViewRow row4 in (IEnumerable)dgv.Rows)
                 {
                     if (!row4.IsNewRow)
                     {
                         rownum++;
                         num5 = num4;
                         IRow row5 = sheet.CreateRow(rownum);
                         foreach (DataGridViewColumn column3 in dgv.Columns)
                         {
                             if (column3.Visible && (column3.CellType != typeof(DataGridViewCheckBoxCell)))
                             {
                                 num5++;
                                 if ((row4.Cells[column3.Index].Value != null) && !row4.IsNewRow)
                                 {
                                     row5.CreateCell(num5).SetCellValue(row4.Cells[column3.Index].Value.ToString());
                                     int num10 = sheet.GetColumnWidth(row5.GetCell(num5).ColumnIndex);
                                     int num11 = (Encoding.GetEncoding("gb2312").GetBytes(row4.Cells[column3.Index].Value.ToString()).Length + num) * num2;
                                     num10 = (num11 < num10) ? num10 : num11;
                                     sheet.SetColumnWidth(num5, num10);
                                     if (column3.CellType == typeof(DataGridViewTextBoxCell))
                                     {
                                         sheet.GetRow(rownum).GetCell(num5).CellStyle = style;
                                     }
                                 }
                             }
                         }
                         continue;
                     }
                 }
             }
             int num12 = rownum + 1;
             num5 = num4;
             foreach (DataGridViewColumn column4 in dgv.Columns)
             {
                 if (column4.Visible)
                 {
                     num5++;
                 }
             }
             num12 = rownum + 2;
             IRow  row6  = sheet.CreateRow(num12);
             ICell cell2 = row6.CreateCell(1);
             cell2.SetCellValue("合计:");
             ICellStyle style2 = workbook.CreateCellStyle();
             style2.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
             style2.VerticalAlignment = VerticalAlignment.CENTER;
             cell2.CellStyle          = style2;
             int columnWidth = sheet.GetColumnWidth(1);
             int num14       = (Encoding.GetEncoding("gb2312").GetBytes("合计:").Length + num) * num2;
             columnWidth = (num14 < columnWidth) ? columnWidth : num14;
             sheet.SetColumnWidth(1, columnWidth);
             cell2 = row6.CreateCell(2);
             cell2.SetCellValue(dgv.Rows.Count.ToString() + "条记录");
             style2                   = workbook.CreateCellStyle();
             style2.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
             style2.VerticalAlignment = VerticalAlignment.CENTER;
             cell2.CellStyle          = style2;
             int width = sheet.GetColumnWidth(2);
             int num16 = (Encoding.GetEncoding("gb2312").GetBytes(dgv.Rows.Count.ToString() + "条记录").Length + num) * num2;
             width = (num16 < columnWidth) ? width : num16;
             sheet.SetColumnWidth(2, width);
             num6 = 1;
             ICell cell3 = sheet.CreateRow(1).CreateCell(num6);
             cell3.SetCellValue(Title);
             ICellStyle style3 = workbook.CreateCellStyle();
             style3.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
             style3.VerticalAlignment = VerticalAlignment.CENTER;
             NPOI.SS.UserModel.IFont font2 = workbook.CreateFont();
             font2.FontName           = "宋体";
             font2.FontHeightInPoints = 11;
             style3.SetFont(font2);
             cell3.CellStyle = style3;
             sheet.AddMergedRegion(new CellRangeAddress(1, 1, num6, num5));
             sheet.GetRow(1).HeightInPoints = 35f;
             if (string.IsNullOrEmpty(savePath))
             {
                 savePath = @"C:\Documents and Settings\Administrator\My Documents\Report.xls";
             }
             FileStream stream = new FileStream(savePath, FileMode.Create);
             workbook.Write(stream);
             stream.Close();
             MessageBox.Show("数据已经成功导出到:" + savePath, "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
         }
     }
     catch (Exception exception)
     {
         MessageBox.Show(exception.Message);
         Record.execFileRecord("导出报表", exception.ToString());
     }
 }
コード例 #24
0
        public ActionResult Torg12(string List)
        {
            var stream   = new MemoryStream();
            var ids      = List.Split <int>(";");
            var orders   = DB.Orders.Where(x => ids.Contains(x.ID) && x.Shop != null && x.Shop.Owner == CurrentUser.ShopOwnerID).ToList();
            var workbook = new HSSFWorkbook();


            using (var fs = new FileStream(Server.MapPath("~/Content/TORG12.xls"), FileMode.Open))
            {
                var example = new HSSFWorkbook(fs);
                int index   = 0;
                foreach (var order in orders)
                {
                    var sheet = (HSSFSheet)example.GetSheetAt(0);

                    sheet.CopyTo(workbook, "Заказ №" + order.OrderNumberOrID.Replace("'", "").Replace("[", "@").Replace("]", "@"), true, false);

                    var copied = (HSSFSheet)workbook.GetSheetAt(index);
                    copied.GetRow(2).GetCell(1).SetCellValue(order.Requisites);
                    copied.GetRow(9).GetCell(3).SetCellValue(order.Requisites);
                    copied.GetRow(7).GetCell(3).SetCellValue(order.Receiver);
                    copied.GetRow(11).GetCell(3).SetCellValue(order.Receiver);
                    copied.GetRow(3).GetCell(37).SetCellValue(order.Shop.GetSetting("OKPO"));
                    copied.GetRow(8).GetCell(37).SetCellValue(order.Shop.GetSetting("OKPO"));
                    copied.GetRow(16).GetCell(10).SetCellValue(order.OrderNumberOrID);
                    copied.GetRow(16).GetCell(14).SetCellValue(DateTime.Now.ToString("dd.MM.yyyy"));

                    if (order.OrderedProducts.Count > 1)
                    {
                        for (int i = 1; i < order.OrderedProducts.Count; i++)
                        {
                            copied.GetRow(22).CopyRowTo(23);
                        }
                    }
                    int oi = 0;
                    foreach (var product in order.OrderedProducts)
                    {
                        var row = copied.GetRow(22 + oi);
                        row.GetCell(1).SetCellValue(oi + 1);
                        row.GetCell(2).SetCellValue(product.Product.Name);
                        row.GetCell(6).SetCellValue(product.Product.Article);
                        row.GetCell(7).SetCellValue(product.Product.AmountUnitName);
                        row.GetCell(11).SetCellValue(product.Product.UnitCode);
                        row.GetCell(21).SetCellValue(product.Amount);
                        row.GetCell(23).SetCellValue((product.PriceWithoutNDS).ToString("f2"));
                        row.GetCell(25).SetCellValue((product.PriceWithoutNDS * product.Amount).ToString("f2"));
                        row.GetCell(30).SetCellValue("18%");
                        row.GetCell(33).SetCellValue((product.NDS * product.Amount).ToString("f2"));
                        row.GetCell(36).SetCellValue((product.Price * product.Amount).ToString("f2"));

                        oi++;
                    }

                    var rws1 = copied.GetRow(22 + oi);
                    var rws2 = copied.GetRow(22 + oi + 1);
                    rws1.GetCell(21).SetCellValue(order.OrderedProducts.Sum(x => x.Amount));
                    rws2.GetCell(21).SetCellValue(order.OrderedProducts.Sum(x => x.Amount));

                    rws1.GetCell(23).SetCellValue("X");
                    rws2.GetCell(23).SetCellValue("X");

                    rws1.GetCell(25).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.PriceWithoutNDS).ToString("f2"));
                    rws2.GetCell(25).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.PriceWithoutNDS).ToString("f2"));

                    rws1.GetCell(30).SetCellValue("X");
                    rws2.GetCell(30).SetCellValue("X");

                    rws1.GetCell(33).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.NDS).ToString("f2"));
                    rws2.GetCell(33).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.NDS).ToString("f2"));

                    rws1.GetCell(36).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.Price).ToString("f2"));
                    rws2.GetCell(36).SetCellValue(order.OrderedProducts.Sum(x => x.Amount * x.Price).ToString("f2"));


                    copied.GetRow(22 + oi + 3)
                    .GetCell(5)
                    .SetCellValue(Russian.ToString(order.OrderedProducts.Count, Gender.Masculine));


                    var all    = order.OrderedProducts.Sum(x => x.Price * x.Amount);
                    var allStr = Russian.ToString((int)all, Gender.Masculine, true) + " рублей " +
                                 ((int)((all - (int)all) * 100)).ToString("d2") + " копеек";
                    copied.GetRow(22 + oi + 13)
                    .GetCell(1)
                    .SetCellValue(allStr);

                    index++;
                }

                workbook.Write(stream);
            }
            return(File(stream.ToArray(), MIMETypeWrapper.GetMIME("xls"),
                        "Torg12_" + List.Trim(';').Replace(";", "_") + ".xls"));
        }
コード例 #25
0
        public void TestSetGetProperties()
        {
            HSSFWorkbook  wb        = new HSSFWorkbook();
            HSSFSheet     sh        = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFTextbox   textbox   = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;

            textbox.String = (new HSSFRichTextString("test"));
            Assert.AreEqual(textbox.String.String, "test");

            textbox.HorizontalAlignment = ((HorizontalAlignment)5);
            Assert.AreEqual((HorizontalAlignment)5, textbox.HorizontalAlignment);

            textbox.VerticalAlignment = ((VerticalAlignment)6);
            Assert.AreEqual((VerticalAlignment)6, textbox.VerticalAlignment);

            textbox.MarginBottom = (7);
            Assert.AreEqual(textbox.MarginBottom, 7);

            textbox.MarginLeft = (8);
            Assert.AreEqual(textbox.MarginLeft, 8);

            textbox.MarginRight = (9);
            Assert.AreEqual(textbox.MarginRight, 9);

            textbox.MarginTop = (10);
            Assert.AreEqual(textbox.MarginTop, 10);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh        = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;
            textbox   = (HSSFTextbox)patriarch.Children[0];
            Assert.AreEqual(textbox.String.String, "test");
            Assert.AreEqual(textbox.HorizontalAlignment, (HorizontalAlignment)5);
            Assert.AreEqual(textbox.VerticalAlignment, (VerticalAlignment)6);
            Assert.AreEqual(textbox.MarginBottom, 7);
            Assert.AreEqual(textbox.MarginLeft, 8);
            Assert.AreEqual(textbox.MarginRight, 9);
            Assert.AreEqual(textbox.MarginTop, 10);

            textbox.String = (new HSSFRichTextString("test1"));
            textbox.HorizontalAlignment = HorizontalAlignment.Center;
            textbox.VerticalAlignment   = VerticalAlignment.Top;
            textbox.MarginBottom        = (71);
            textbox.MarginLeft          = (81);
            textbox.MarginRight         = (91);
            textbox.MarginTop           = (101);

            Assert.AreEqual(textbox.String.String, "test1");
            Assert.AreEqual(textbox.HorizontalAlignment, HorizontalAlignment.Center);
            Assert.AreEqual(textbox.VerticalAlignment, VerticalAlignment.Top);
            Assert.AreEqual(textbox.MarginBottom, 71);
            Assert.AreEqual(textbox.MarginLeft, 81);
            Assert.AreEqual(textbox.MarginRight, 91);
            Assert.AreEqual(textbox.MarginTop, 101);

            wb        = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh        = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;
            textbox   = (HSSFTextbox)patriarch.Children[0];

            Assert.AreEqual(textbox.String.String, "test1");
            Assert.AreEqual(textbox.HorizontalAlignment, HorizontalAlignment.Center);
            Assert.AreEqual(textbox.VerticalAlignment, VerticalAlignment.Top);
            Assert.AreEqual(textbox.MarginBottom, 71);
            Assert.AreEqual(textbox.MarginLeft, 81);
            Assert.AreEqual(textbox.MarginRight, 91);
            Assert.AreEqual(textbox.MarginTop, 101);
        }
コード例 #26
0
        public async Task <ActionResult> ExportToExcel(MaterialReceiptDetailQueryViewModel model)
        {
            IList <MaterialReceiptDetail> lst = new List <MaterialReceiptDetail>();

            using (MaterialReceiptServiceClient client = new MaterialReceiptServiceClient())
            {
                await Task.Run(() =>
                {
                    PagingConfig cfg = new PagingConfig()
                    {
                        IsPaging = false,
                        OrderBy  = "CreateTime Desc,Key.ReceiptNo,Key.ItemNo",
                        Where    = GetWhereCondition(model)
                    };
                    MethodReturnResult <IList <MaterialReceiptDetail> > result = client.GetDetail(ref cfg);

                    if (result.Code == 0)
                    {
                        lst = result.Data;
                    }
                });
            }
            //创建工作薄。
            IWorkbook wb = new HSSFWorkbook();
            //设置EXCEL格式
            ICellStyle style = wb.CreateCellStyle();

            style.FillForegroundColor = 10;
            //有边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft   = BorderStyle.Thin;
            style.BorderRight  = BorderStyle.Thin;
            style.BorderTop    = BorderStyle.Thin;
            IFont font = wb.CreateFont();

            font.Boldweight = 10;
            style.SetFont(font);
            ICell  cell = null;
            IRow   row  = null;
            ISheet ws   = null;

            for (int j = 0; j < lst.Count; j++)
            {
                if (j % 65535 == 0)
                {
                    ws  = wb.CreateSheet();
                    row = ws.CreateRow(0);
                    #region //列名
                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptNo);  //领料号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_OrderNumber);  //工单号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptDate);  //领料日期

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_ItemNo);  //项目号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_LineStoreName);  //线别仓

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialCode);  //物料编码

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("物料名称");  //物料名称

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialLot);  //物料批号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_Qty);  //数量

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierMaterialLot);  //供应商批号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierCode);  //供应商编码

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("供应商名称");  //供应商名称

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("效率档");  //效率档

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("描述");  //描述

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("编辑人");  //编辑人

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("编辑时间");  //编辑时间
                    #endregion
                    font.Boldweight = 5;
                }

                MaterialReceiptDetail obj   = lst[j];
                MaterialReceipt       mrObj = model.GetMaterialReceipt(obj.Key.ReceiptNo);
                Material m = model.GetMaterial(obj.MaterialCode);
                Supplier s = model.GetSupplier(obj.SupplierCode);
                row = ws.CreateRow(j + 1);

                #region //数据
                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Key.ReceiptNo);  //领料号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(mrObj == null ? string.Empty : mrObj.OrderNumber);  //工单号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(mrObj == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", mrObj.ReceiptDate));  //领料日期

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Key.ItemNo);  //项目号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.LineStoreName);  //线别仓

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.MaterialCode);  //物料编码

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(m == null ? string.Empty : m.Name);  //物料名称

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.MaterialLot);  //物料批号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Qty);  //数量

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.SupplierMaterialLot);  //供应商批号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.SupplierCode);  //供应商编码

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Attr1);  //效率档

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Description);  //描述

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Editor);  //编辑人

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime));  //编辑时间
                #endregion
            }

            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return(File(ms, "application/vnd.ms-excel", "MaterialReceiptData.xls"));
        }
コード例 #27
0
ファイル: ExcelExtractor.cs プロジェクト: okevin/chama
 /// <summary>
 /// Initializes a new instance of the <see cref="ExcelExtractor"/> class.
 /// </summary>
 /// <param name="wb">The wb.</param>
 public ExcelExtractor(HSSFWorkbook wb)
     : base(wb)
 {
     this.wb    = wb;
     _formatter = new HSSFDataFormatter();
 }
コード例 #28
0
ファイル: IEnumerableEx.cs プロジェクト: wxytkzc/GitHubCode
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T">必须是有构造函数的实体类</typeparam>
        /// <param name="dtSource"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="writeColumnName"></param>
        /// <param name="excelType"></param>
        public static void WriteStringsInToExcel(this IEnumerable <string> dtSource, string fileName, string sheetName, bool writeColumnName = true, string columnName = "Null Column Title", ExcelType excelType = ExcelType.XLS)
        {
            FileStream fs       = null;
            IWorkbook  workbook = null;
            ISheet     sheet    = null;

            try
            {
                #region 初始化
                if (excelType == ExcelType.XLS)
                {
                    workbook = new HSSFWorkbook();
                    if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
                    {
                        fileName = fileName + ".xls";
                    }
                }
                else
                {
                    workbook = new XSSFWorkbook();
                    if (fileName.Substring(fileName.Length - 4, 4).ToLower() != ".xls")
                    {
                        fileName = fileName + ".xlsx";
                    }
                }

                if (workbook != null)
                {
                    if (sheet == null)
                    {
                        sheet = workbook.CreateSheet(sheetName);
                    }
                }
                fs = new FileStream(fileName + ".", FileMode.OpenOrCreate, FileAccess.ReadWrite);
                #endregion

                var list = dtSource.ToList();

                #region 写列名称和内容
                if (writeColumnName)
                {
                    list.Insert(0, columnName);
                }
                var rowBeginIndex = 0;
                list.ForEach(x => {
                    IRow row = sheet.CreateRow(rowBeginIndex);
                    var cell = row.CreateCell(0, CellType.String);
                    cell.CellStyle.WrapText = true;
                    if (rowBeginIndex == 0 && writeColumnName)
                    {
                        ICellStyle style = workbook.CreateCellStyle();
                        //设置单元格的样式:水平对齐居中,有边框
                        style.Alignment         = HorizontalAlignment.Center;
                        style.VerticalAlignment = VerticalAlignment.Center;

                        style.FillForegroundColor = HSSFColor.White.Index;
                        style.FillBackgroundColor = HSSFColor.Red.Index; //指定背景颜色
                        //将新的样式赋给单元格
                        cell.CellStyle = style;
                    }
                    cell.SetCellValue(x);
                    rowBeginIndex++;
                });
                #endregion

                workbook.Write(fs); //写入到excel
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                fs.Dispose();
                fs.Close();
            }
        }
コード例 #29
0
        //Datatable导出Excel
        public static void DatatableToExcelByNPOI(DataTable dt, string strExcelFileName)
        {
            HSSFWorkbook workbook = null;

            try
            {
                workbook = new HSSFWorkbook();
                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);


                //用column name 作为列名
                int  icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.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  = 1;
                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();

                //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                //ILog log = LogManager.GetLogger("Exception Log");
                _logger.Error(ex.Message + Environment.NewLine + ex.StackTrace);
                //记录AuditTrail
                //CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);

                //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally { workbook = null; }
        }
コード例 #30
0
        public byte[] Export(DataTable dt, IDictionary<string, string> columns)
        {
            //HttpResponse rsp = HttpContext.Current.Response;

            HSSFWorkbook wb = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            ISheet sheet = wb.CreateSheet("Sheet1");

            //填充表头
            IRow dataRow = sheet.CreateRow(0);

            bool isReColumn = !(columns == null || columns.Count == 0);

            int tmpInt = 0;

            if (isReColumn)
            {
                foreach (string columnName in columns.Keys)
                {
                    dataRow.CreateCell(tmpInt++).SetCellValue(columns[columnName]);
                }
            }
            else
            {
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(tmpInt++).SetCellValue(column.ColumnName);
                }
            }

            //填充内容
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dataRow = sheet.CreateRow(i + 1);

                if (isReColumn)
                {
                    tmpInt = 0;
                    foreach (string columnName in columns.Keys)
                    {
                        dataRow.CreateCell(tmpInt++).SetCellValue(dt.Rows[i][columnName].ToString());
                    }
                }
                else
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
            }

            wb.Write(ms);
            //rsp.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
            //rsp.BinaryWrite(ms.ToArray());

            wb = null;
            ms.Close();
            byte[] bytes = ms.ToArray();
            ms.Dispose();

            return bytes;
        }
コード例 #31
0
ファイル: ExcelHelper.cs プロジェクト: GuChengNo-1/SouKeBao
        public static DataTable ExcelToDataTable(string path)
        {
            IWorkbook workbook = null;
            ISheet    sheet    = null;
            IRow      row      = null;
            ICell     cell     = null;

            DataTable table   = new DataTable();
            DataRow   dataRow = null;


            try
            {
                using (FileStream fs = File.OpenRead(path))
                {
                    // Path.GetExtension(path) 获取文件的后缀名
                    if (Path.GetExtension(path) == ".xlsx")
                    {
                        //从流中获取excel 工作簿
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs);
                    }

                    //从工作簿里获取指定的表
                    sheet = workbook.GetSheetAt(0);

                    for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
                    {
                        //根据excel表里的列数为datatable创建列
                        table.Columns.Add(new DataColumn());
                    }

                    //根据表格的行数进行循环创建dataRow
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        //为datatable创建行
                        dataRow = table.NewRow();
                        //从excel表中获取指定的行
                        row = sheet.GetRow(i);

                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //从行里获取单元格
                            cell = row.GetCell(j);
                            switch (cell.CellType)
                            {
                            //如果excel单元格内的值的数据类型为 数值
                            case CellType.Numeric:
                                //将excel表中单元格的值赋给datatable行中的某列
                                dataRow[j] = cell.NumericCellValue;
                                break;

                            case CellType.String:
                                dataRow[j] = cell.StringCellValue;
                                break;

                            case CellType.Blank:
                                dataRow[j] = "";
                                break;

                            default:
                                throw new Exception("格式错误");
                            }
                        }
                        //将创建的行添加到datatable的rows(行集合)
                        table.Rows.Add(dataRow);
                    }
                    //返回结果
                    return(table);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
コード例 #32
0
        ///// <summary>
        ///// 给指定单元格写入内容
        ///// </summary>
        ///// <param name="workBook"></param>
        ///// <param name="sheetName"></param>
        ///// <param name="rowNum"></param>
        ///// <param name="colNum"></param>
        ///// <param name="content"></param>
        //public void WriteCell(HSSFWorkbook workBook, string sheetName, int rowNum, int colNum, string content)
        //{
        //    if (workBook == null)
        //    {
        //        throw new Exception("workBook不能为null");
        //    }
        //    WriteCell(workBook, workBook.GetSheetIndex(sheetName), rowNum, colNum, content);
        //}

        ///// <summary>
        ///// 给指定单元格写入内容
        ///// </summary>
        ///// <param name="workBook"></param>
        ///// <param name="sheetNum"></param>
        ///// <param name="rowNum"></param>
        ///// <param name="colNum"></param>
        ///// <param name="content"></param>
        ///// <returns></returns>
        //public void WriteCell(HSSFWorkbook workBook, int sheetNum, int rowNum, int colNum, string content)
        //{
        //    if (workBook == null)
        //    {
        //        throw new Exception("workBook不能为null");
        //    }

        //    if (workBook.NumberOfSheets < sheetNum || sheetNum < 0)
        //    {
        //        throw new Exception("指定的sheet不存在");
        //    }

        //    ISheet sheet = workBook.GetSheetAt(sheetNum - 1);

        //    HSSFRow row = (HSSFRow)sheet.GetRow(rowNum) ?? (HSSFRow)sheet.CreateRow(rowNum - 1);
        //    HSSFCell cell = (HSSFCell)row.CreateCell(6);
        //    cell.SetCellValue(content);

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

        //    //    using (FileStream fs = new FileStream("测试行列写入.xls", FileMode.Create, FileAccess.Write))
        //    //    {
        //    //        byte[] data = ms.ToArray();
        //    //        fs.Write(data, 0, data.Length);
        //    //        fs.Flush();
        //    //    }
        //    //}
        //    //return workBook;
        //}
        #endregion

        /// <summary>
        /// 设置Excel文件属性信息
        /// </summary>
        /// <param name="workbook"></param>
        private void SetFileProperty(HSSFWorkbook workbook)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = this.Company;
            dsi.Scale = this.Scale;
            dsi.Manager = this.Manager;
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = this.Author;
            si.ApplicationName = this.ApplicationName;
            si.LastAuthor = this.LastAuthor;
            si.Comments = this.Comments;
            si.Title = this.Title;
            si.Subject = this.Subject;
            si.CreateDateTime = this.CreateDateTime ?? DateTime.Now;
            si.Keywords = this.Keywords;
            si.LastAuthor = this.LastAuthor;
            si.LastPrinted = this.LastPrinted;
            si.LastSaveDateTime = this.LastSaveDateTime ?? DateTime.Now;

            workbook.SummaryInformation = si;
        }
コード例 #33
0
        void ReadFile(string filePath, string fileExtension)
        {
            StringBuilder sb = new StringBuilder();
            ISheet        sheet;

            using (var stream = new FileStream(filePath, FileMode.Open))
            {
                stream.Position = 0;
                if (fileExtension == ".xls")
                {
                    HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
                    sheet = hssfwb.GetSheetAt(0);                   //get first sheet from workbook
                }
                else
                {
                    XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
                    sheet = hssfwb.GetSheetAt(0);                   //get first sheet from workbook
                }

                IRow headerRow = sheet.GetRow(0); //Get Header Row
                int  cellCount = headerRow.LastCellNum;
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
                    {
                        continue;
                    }

                    this.Header.Add(cell.ToString());
                }

                //for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
                int rowId = 1;
                for (int i = sheet.LastRowNum; i > (sheet.FirstRowNum + 1); i--) //Read Excel File
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    if (row.Cells.All(d => d.CellType == CellType.Blank))
                    {
                        continue;
                    }

                    TransactionExtended tr = new TransactionExtended();

                    tr.Id             = rowId;
                    tr.AccountingDate = row.GetCell(0).DateCellValue;
                    tr.TransactionId  = row.GetCell(1).ToString();
                    tr.Type           = row.GetCell(2).ToString();
                    tr.Account        = row.GetCell(3).ToString();
                    tr.AccountName    = row.GetCell(4).ToString();
                    tr.PartnerAccount = row.GetCell(5).ToString();
                    tr.PartnerName    = row.GetCell(6).ToString();
                    tr.Sum            = decimal.Parse(row.GetCell(7).ToString());
                    tr.Currency       = row.GetCell(7).ToString();
                    tr.Message        = row.GetCell(9).ToString();

                    rowId++;
                }
            }
        }
コード例 #34
0
ファイル: HSSFChart.cs プロジェクト: 89sos98/npoi
	/**
	 * Creates a bar chart.  API needs some work. :)
	 * <p>
	 * NOTE:  Does not yet work...  checking it in just so others
	 * can take a look.
	 */
	public void CreateBarChart( HSSFWorkbook workbook, HSSFSheet sheet )
	{

		List<Record> records = new List<Record>();
		records.Add( CreateMSDrawingObjectRecord() );
		records.Add( CreateOBJRecord() );
		records.Add( CreateBOFRecord() );
		records.Add(new HeaderRecord(""));
		records.Add(new FooterRecord(""));
		records.Add( CreateHCenterRecord() );
		records.Add( CreateVCenterRecord() );
		records.Add( CreatePrintSetupRecord() );
		// unknown 33
		records.Add( CreateFontBasisRecord1() );
		records.Add( CreateFontBasisRecord2() );
		records.Add(new ProtectRecord(false));
		records.Add( CreateUnitsRecord() );
		records.Add( CreateChartRecord( 0, 0, 30434904, 19031616 ) );
		records.Add( CreateBeginRecord() );
		records.Add( CreateSCLRecord( (short) 1, (short) 1 ) );
		records.Add( CreatePlotGrowthRecord( 65536, 65536 ) );
		records.Add( CreateFrameRecord1() );
		records.Add( CreateBeginRecord() );
		records.Add( CreateLineFormatRecord(true) );
		records.Add( CreateAreaFormatRecord1() );
		records.Add( CreateEndRecord() );
		records.Add( CreateSeriesRecord() );
		records.Add( CreateBeginRecord() );
		records.Add( CreateTitleLinkedDataRecord() );
		records.Add( CreateValuesLinkedDataRecord() );
		records.Add( CreateCategoriesLinkedDataRecord() );
		records.Add( CreateDataFormatRecord() );
		//		records.add(createBeginRecord());
		// unknown
		//		records.add(createEndRecord());
		records.Add( CreateSeriesToChartGroupRecord() );
		records.Add( CreateEndRecord() );
		records.Add( CreateSheetPropsRecord() );
		records.Add( CreateDefaultTextRecord( DefaultDataLabelTextPropertiesRecord.CATEGORY_DATA_TYPE_ALL_TEXT_CHARACTERISTIC ) );
		records.Add( CreateAllTextRecord() );
		records.Add( CreateBeginRecord() );
		// unknown
		records.Add( CreateFontIndexRecord( 5 ) );
		records.Add( CreateDirectLinkRecord() );
		records.Add( CreateEndRecord() );
		records.Add( CreateDefaultTextRecord( (short) 3 ) ); // eek, undocumented text type
		records.Add( CreateUnknownTextRecord() );
		records.Add( CreateBeginRecord() );
		records.Add( CreateFontIndexRecord( (short) 6 ) );
		records.Add( CreateDirectLinkRecord() );
		records.Add( CreateEndRecord() );

		records.Add( CreateAxisUsedRecord( (short) 1 ) );
		CreateAxisRecords( records );

		records.Add( CreateEndRecord() );
		records.Add( CreateDimensionsRecord() );
		records.Add( CreateSeriesIndexRecord(2) );
		records.Add( CreateSeriesIndexRecord(1) );
		records.Add( CreateSeriesIndexRecord(3) );
		records.Add(EOFRecord.instance);



		sheet.InsertChartRecords( records );
		workbook.InsertChartRecord();
	}
コード例 #35
0
    protected void ExportExcel(IList <EDIFordPlan> exportList)
    {
        HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        //Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
        MemoryStream output = new MemoryStream();

        if (exportList != null && exportList.Count > 0)
        {
            var groups = (from tak in exportList
                          group tak by tak.Control_Num into result
                          select new
            {
                Control_Num = result.Key,
                List = result.ToList()
            }).ToList();
            for (int ti = 0; ti < groups.Count(); ti++)
            {
                var              g           = groups[ti];
                Sheet            sheet1      = hssfworkbook.CreateSheet(g.Control_Num);
                IList <DateTime> dateListDic = g.List.OrderBy(l => l.ForecastDate).Select(l => l.ForecastDate).Distinct().ToList();
                #region 写入字段
                Row rowHeader = sheet1.CreateRow(0);
                for (int i = 0; i < 6 + dateListDic.Count; i++)
                {
                    if (i == 0) //版本号
                    {
                        rowHeader.CreateCell(i).SetCellValue("版本号");
                    }
                    else if (i == 1)  //文件发布日期
                    {
                        rowHeader.CreateCell(i).SetCellValue("文件发布日期");
                    }
                    else if (i == 2) //物料号
                    {
                        rowHeader.CreateCell(i).SetCellValue("物料号");
                    }
                    else if (i == 3)    //物料描述
                    {
                        rowHeader.CreateCell(i).SetCellValue("物料描述");
                    }
                    else if (i == 4)    //福特物料号
                    {
                        rowHeader.CreateCell(i).SetCellValue("福特物料号");
                    }
                    else if (i == 5)      //单位
                    {
                        rowHeader.CreateCell(i).SetCellValue("单位");
                    }
                    else
                    {
                        foreach (var date in dateListDic)
                        {
                            rowHeader.CreateCell(i++).SetCellValue(date.ToShortDateString());
                        }
                    }
                }
                #endregion

                #region 写入数值
                //Caption.Visible = true;
                var groupByRefItem = (from tak in g.List
                                      group tak by tak.RefItem into result
                                      select new
                {
                    RefItem = result.Key,
                    List = result.ToList()
                }).ToList();
                IList <FlowDetail> flowdets = TheGenericMgr.FindAllWithCustomQuery <FlowDetail>(string.Format(" select d from FlowDetail as d where  d.ReferenceItemCode in('{0}') ", string.Join("','", g.List.Select(w => w.RefItem).Distinct().ToArray())));
                int j = 1;
                foreach (var d in groupByRefItem)
                {
                    Row         rowDetail  = sheet1.CreateRow(j);
                    Row         rowDetail2 = sheet1.CreateRow(j + 1);
                    EDIFordPlan newPlan    = d.List.First();
                    if (flowdets != null && flowdets.Count > 0)
                    {
                        var flowDet = flowdets.Where(f => f.ReferenceItemCode == newPlan.RefItem);
                        if (flowDet != null && flowDet.Count() > 0)
                        {
                            newPlan.Item     = flowDet.First().Item.Code;
                            newPlan.ItemDesc = flowDet.First().Item.Description;
                        }
                    }
                    rowDetail.CreateCell(0).SetCellValue(newPlan.Control_Num);
                    rowDetail.CreateCell(1).SetCellValue(newPlan.ReleaseIssueDate.ToShortDateString());
                    rowDetail.CreateCell(2).SetCellValue(newPlan.Item);
                    rowDetail.CreateCell(3).SetCellValue(newPlan.ItemDesc);
                    rowDetail.CreateCell(4).SetCellValue(newPlan.RefItem);
                    rowDetail.CreateCell(5).SetCellValue(newPlan.Uom);
                    int cell = 0;
                    foreach (var f in d.List.OrderBy(o => o.ForecastDate))
                    {
                        cell++;
                        //rowDetail.CreateCell(5 + cell).
                        var createCell = rowDetail.CreateCell(5 + cell);
                        createCell.SetCellType(CellType.NUMERIC);
                        createCell.SetCellValue(Convert.ToDouble(f.ForecastQty > 0 ? f.ForecastQty : 0));

                        var createCell2 = rowDetail2.CreateCell(5 + cell);
                        createCell2.SetCellType(CellType.NUMERIC);
                        createCell2.SetCellValue(Convert.ToDouble(f.ForecastCumQty > 0 ? f.ForecastCumQty : 0));
                    }
                    j += 2;
                }
                #endregion
            }
            hssfworkbook.Write(output);

            string filename = "FordEdiPlan.xls";
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            Response.Clear();

            Response.BinaryWrite(output.GetBuffer());
            Response.End();
            //return File(output, contentType, exportName + "." + fileSuffiex);
        }
    }