示例#1
0
        /// <summary>
        /// 把DataTable 转为Excel 内容
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="startRow"></param>
        /// <param name="endRow"></param>
        /// <param name="book"></param>
        /// <param name="sheetName"></param>
        private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName)
        {
            //头部样式
            CellStyle headstyle = book.CreateCellStyle();

            headstyle.Alignment         = HorizontalAlignment.CENTER;
            headstyle.VerticalAlignment = VerticalAlignment.CENTER;
            Font headfont = book.CreateFont();

            headfont.Boldweight = 700;
            headstyle.SetFont(headfont);
            headstyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
            headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            headstyle.BorderBottom        = CellBorderType.THIN;
            headstyle.BorderLeft          = CellBorderType.THIN;
            headstyle.BorderRight         = CellBorderType.THIN;
            headstyle.BorderTop           = CellBorderType.THIN;

            NPOI.SS.UserModel.Sheet sheet  = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.Row   header = sheet.CreateRow(0);
            header.Height = 20 * 20;

            //表格内容样
            CellStyle dataStyle = book.CreateCellStyle();

            dataStyle.BorderBottom = CellBorderType.THIN;
            dataStyle.FillPattern  = FillPatternType.SOLID_FOREGROUND;
            dataStyle.BorderLeft   = CellBorderType.THIN;
            dataStyle.BorderRight  = CellBorderType.THIN;
            dataStyle.BorderTop    = CellBorderType.THIN;



            for (int i = 0; i < dt.Columns.Count; i++)
            {
                NPOI.SS.UserModel.Cell cell = header.CreateCell(i);
                cell.CellStyle = headstyle;
                string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
                cell.SetCellValue(val);
            }

            int rowIndex = 1;

            for (int i = startRow; i <= endRow; i++)
            {
                DataRow dtRow = dt.Rows[i];
                NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++);

                for (int j = 0; j < dtRow.ItemArray.Length; j++)
                {
                    excelRow.CreateCell(j).CellStyle = dataStyle;
                    excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
                }
            }
        }
示例#2
0
        protected void CreateDataHeader(Sheet worksheet)
        {
            StyleManager.InitStaticStyle();
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 2));
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5));
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 6, 8));

            int colindex = 0;

            NPOI.SS.UserModel.Row header = worksheet.CreateRow(0);
            StyleManager.SetCenterBoldStringCell(header.CreateCell(colindex++)).SetCellValue("Daily AR&AP Interface");
            header.Height = 450;

            NPOI.SS.UserModel.Row header1 = worksheet.CreateRow(1);
            colindex = 0;
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Newegg.com.cn");
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Posting Date: " + end);
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("MTD( " + begin + " - " + end + " )");
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);

            NPOI.SS.UserModel.Row header2 = worksheet.CreateRow(2);
            colindex = 0;
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data");
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table");
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance");
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data");
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table");
            StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance");

            NPOI.SS.UserModel.Row header3 = worksheet.CreateRow(3);
            colindex = 0;
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("AcctType");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("DocumentType");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("CompanyCode");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)");
            StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)");
        }
示例#3
0
        /// <summary>
        /// Get a specific cell from a row. If the cell doesn't exist,
        /// </summary>
        /// <param name="row">The row that the cell is part of</param>
        /// <param name="column">The column index that the cell is in.</param>
        /// <returns>The cell indicated by the column.</returns>
        public static NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Row row, int column)
        {
            NPOI.SS.UserModel.Cell cell = row.GetCell(column);

            if (cell == null)
            {
                cell = row.CreateCell(column);
            }
            return(cell);
        }
示例#4
0
        protected void CreateDataCells(Sheet worksheet, List <SAPInterfaceExchangeInfo> dataList)
        {
            ushort currentRowIndex = 4;
            ushort currentColIndex = 0;

            dataList.ForEach(delegate(SAPInterfaceExchangeInfo item)
            {
                NPOI.SS.UserModel.Row data = worksheet.CreateRow(currentRowIndex++);
                currentColIndex            = 0;
                StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.AcctTypeDisplay);
                StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.DocumentType);
                StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.CompanyCode);
                StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.Legacy_GLAmount));
                StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.SAP_GLAmount));
                StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.DateBalance));
                StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDLegacy_GLAmount));
                StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDSAP_GLAmount));
                StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDBalance));
            });
        }
示例#5
0
    protected void ExportResult(IList <TransportationBill> tbillList)
    {
        HSSFWorkbook excel = new HSSFWorkbook();

        NPOI.SS.UserModel.Sheet sheet = excel.CreateSheet("TransportationBill");
        NPOI.SS.UserModel.Row   row   = sheet.CreateRow(0);
        row.CreateCell(0).SetCellValue("BILL NO");
        row.CreateCell(1).SetCellValue("ShipOrderNo");
        row.CreateCell(2).SetCellValue("单价");
        row.CreateCell(3).SetCellValue("开票数");
        row.CreateCell(4).SetCellValue("金额");
        int rowNum = 1;

        foreach (TransportationBill bill in tbillList)
        {
            foreach (TransportationBillDetail bd in bill.TransportationBillDetails)
            {
                NPOI.SS.UserModel.Row _row = sheet.CreateRow(rowNum);
                _row.CreateCell(0).SetCellValue(bd.Bill.BillNo);
                _row.CreateCell(1).SetCellValue(bd.ActBill.OrderNo);
                _row.CreateCell(2).SetCellValue((double)bd.UnitPrice);
                _row.CreateCell(3).SetCellValue((double)bd.BilledQty);
                _row.CreateCell(4).SetCellValue((double)bd.Amount);
                rowNum++;
            }
        }
        MemoryStream ms = new MemoryStream();

        excel.Write(ms);
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TBillResult.xls"));
        Response.BinaryWrite(ms.ToArray());

        excel = null;
        ms.Close();
        ms.Dispose();
    }
示例#6
0
        /// <summary>
        /// Gets the cell to manage it.
        /// </summary>
        /// <param name="rowPos">The row pos.</param>
        /// <param name="columnPos">The column pos.</param>
        /// <returns>Cell of the spreadsheet</returns>
        public NPOI.SS.UserModel.Cell GetCell(int rowPos, int columnPos)
        {
            NPOI.SS.UserModel.Row row = _sheet.GetRow(rowPos);

            if (row == null)
            {
                AddRow(rowPos);
                row = _sheet.GetRow(rowPos);
            }

            //Creating the cell
            NPOI.SS.UserModel.Cell cell = row.GetCell(columnPos);

            if (cell == null)
            {
                row.CreateCell(columnPos);
                cell = row.GetCell(columnPos);
            }

            return(cell);
        }
示例#7
0
        public override void OutPut(DataTable dt)
        {
            string fileExt      = DateTime.Now.ToString("yyyyMMddHHmmss");
            string fileOutPut   = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt);
            string saveFileName = fileOutPut;
            //SaveFileDialog saveDialog = new SaveFileDialog();
            //saveDialog.DefaultExt = "xls";
            //saveDialog.Filter = "Excel文件|*.xls";
            //saveDialog.FileName = fileName;
            //saveDialog.ShowDialog();
            //saveFileName = saveDialog.FileName;

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

            Sheet sheet = workbook.CreateSheet("Sheet1");

            NPOI.SS.UserModel.Row  dataTableName = sheet.CreateRow(0);
            NPOI.SS.UserModel.Cell cellTableName = dataTableName.CreateCell(0);
            cellTableName.SetCellValue(dt.TableName);
            //sheet.SetActiveCellRange(0, 2, 0, dt.Columns.Count);
            int rangeID = sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, dt.Columns.Count));

            NPOI.SS.UserModel.Row dataRowColumnName = sheet.CreateRow(3);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                NPOI.SS.UserModel.Cell cellName = dataRowColumnName.CreateCell(j);
                cellName.SetCellValue(dt.Columns[j].ColumnName);
                NPOI.SS.UserModel.Name sheetName = workbook.CreateName();
                sheetName.NameName   = dt.Columns[j].ColumnName;
                sheetName.SheetIndex = 0;
            }

            int rowCount = dt.Rows.Count;
            int colCount = dt.Columns.Count;

            for (int i = 0; i < rowCount; i++)
            {
                NPOI.SS.UserModel.Row dataRow = sheet.CreateRow(4 + i);
                for (int j = 0; j < colCount; j++)
                {
                    NPOI.SS.UserModel.Cell cell = dataRow.CreateCell(j);

                    if (dt.Rows[i][j].GetType().Equals(typeof(DateTime)))
                    {
                        cell.SetCellValue(((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString()); //项目序号
                    }
                }
            }

            workbook.Write(ms);
            FileStream file = new FileStream(saveFileName, FileMode.Create);

            workbook.Write(file);
            file.Close();
            workbook = null;
            ms.Close();
            ms.Dispose();

            if (MessageBox.Show("Excel导出成功:" + fileOutPut + "\r\n是否要打开?", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(fileOutPut);
            }
        }
示例#8
0
        public static void ExportToExcelExtended(DataTable table, string Name)
        {
            //ExcelHelper excel = new ExcelHelper();
            //excel.CreateFile(Name);
            //excel.CreateSheet(Name);

            InitializeWorkbook();

            Sheet sheet = hssfworkbook.CreateSheet("new sheet");

            //ExcelDocument document = new ExcelDocument();
            //document.UserName = "******";
            //document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;

            //document.ColumnWidth(0, 120);
            //document.ColumnWidth(1, 80);

            //document[0, 0].Value = Name;
            //document[0, 0].Font = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold);
            //document[0, 0].ForeColor = ExcelColor.DarkRed;
            //document[0, 0].Alignment = Alignment.Centered;
            //document[0, 0].BackColor = ExcelColor.Silver;

            int i = 1; //стрічка
            int j = 1; //колонка

            NPOI.SS.UserModel.Row header = sheet.CreateRow(0);

            foreach (DataColumn column in table.Columns)
            {
                NPOI.SS.UserModel.Cell cell = header.CreateCell(j);
                cell.SetCellValue(column.ColumnName);
                j++;
            }

            j = 1;

            foreach (DataRow r in table.Rows)
            {
                j = 1;
                //NPOI.SS.UserModel.Row row = excel.CreateRow(i);
                NPOI.SS.UserModel.Row row = sheet.CreateRow(i);
                foreach (DataColumn column in table.Columns)
                {
                    NPOI.SS.UserModel.Cell cell = row.CreateCell(j);

                    Type dataType = column.DataType;



                    switch (dataType.Name)
                    {
                    case "String":
                    {
                        cell.SetCellValue(r.ItemArray[j - 1].ToString());
                        break;
                    }

                    case "DateTime":
                    {
                        DateTime date = (Convert.ToDateTime(r.ItemArray[j - 1]));
                        cell.SetCellValue(date);
                        CellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyyy-MM-dd h:mm:ss");

                        cell.CellStyle = cellStyle;


                        break;
                    }

                    case "Decimal":
                    {
                        if (r.ItemArray[j - 1] != null)
                        {
                            if (r.ItemArray[j - 1] != null)
                            {
                                if (r.ItemArray[j - 1].ToString().Length > 0)
                                {
                                    cell.SetCellValue(Convert.ToDouble(r.ItemArray[j - 1]));
                                }
                            }
                        }
                        break;
                    }

                    default:
                    {
                        cell.SetCellValue(r.ItemArray[j - 1].ToString());
                        break;
                    }
                    }
                    //excel.SetCellValue( r.ItemArray[j]
                    j++;
                }

                //foreach (object item in r.ItemArray)
                //{
                //    //document.Cell(i,j).Format
                //    excel.CreateCell(j);

                //    excel.SetCellValue( item == null ? "" : (item);

                //    j++;
                //}

                i++;
            }

            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            saveFileDialog1.FileName   = Name;
            saveFileDialog1.DefaultExt = "xls";
            saveFileDialog1.ShowDialog();

            //FileStream stream = new FileStream(saveFileDialog1.FileName, FileMode.Create);

            WriteToFile(saveFileDialog1.FileName);


            //System.Diagnostics.Process.Start(saveFileDialog1.FileName);
        }
示例#9
0
        /// <summary>
        /// 导出DataTable到Excel文件
        /// </summary>
        /// <param name="filename">文件名称</param>
        /// <param name="dtTable">数据DataTable</param>
        /// <returns>文件保存路径</returns>
        public string ExportFile(string filename, List <dynamic> data, string webPath = "")
        {
            NPOI.SS.UserModel.Row rowItem = null;
            int rowCnt = 0;

            foreach (dynamic dr in data)
            {
                if (rowCnt == 0)
                {
                    //创建表头
                    rowItem = _sheet.CreateRow(rowCnt);
                    int i = 0;
                    foreach (KeyValuePair <string, object> dynamicItem in dr)
                    {
                        if (_colName.ContainsKey(dynamicItem.Key))
                        {
                            rowItem.CreateCell(i).SetCellValue(_colName[dynamicItem.Key]);
                        }
                        else
                        {
                            rowItem.CreateCell(i).SetCellValue(dynamicItem.Key);
                        }
                        i++;
                    }
                    rowCnt = rowCnt + 1;
                }

                rowItem = _sheet.CreateRow(rowCnt);

                int k = 0;
                foreach (KeyValuePair <string, object> dynamicItem in dr)
                {
                    string sItemVal = "";
                    if (dynamicItem.Value != null)
                    {
                        sItemVal = dynamicItem.Value.ToString().Trim();
                    }
                    rowItem.CreateCell(k).SetCellValue(sItemVal);
                    k++;
                }

                rowCnt = rowCnt + 1;
            }

            //保存文件
            if (webPath == "")
            {
                webPath = System.Web.HttpContext.Current.Server.MapPath("/");
            }
            //文件名
            string strFileName = string.Format("{0}_{1}_{2}.xls", filename.ToString(),
                                               DateTime.Now.ToString("yyyyMMddHHmm"), CommonLib.Helper.GetRandomNum());
            //文件路径
            string strFilePath = "\\ExportFile\\" + _className + "\\" + DateTime.Now.ToString("yyyyMM") + "\\";

            if (!Directory.Exists(webPath + strFilePath))
            {
                Directory.CreateDirectory(webPath + strFilePath);
            }
            var fileInfo = new FileStream(webPath + strFilePath + strFileName, FileMode.Create);

            Workbook.Write(fileInfo);
            fileInfo.Close();

            return(strFilePath + strFileName);
        }