C# HSSFWorkbook.CreateDataFormat方法代码示例

        private void FillRows(HSSFWorkbook workbook, ISheet workSheet, List<Cell> cells, dynamic rows)
        {
            var dateStyle = workbook.CreateCellStyle();
            var format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            var currentRow = 1;

            if (rows == null)
            {
                return;
            }
            foreach (var row in rows)
            {
                var workRow = workSheet.CreateRow(currentRow);
                var currentCol = 0;
                var type = row.GetType();

                foreach (var cell in cells)
                {
                    var workCell = workRow.CreateCell(currentCol);
                    SetCellValue(workbook, workCell, type, row, cell);

                    currentCol++;
                }
                currentRow++;
            }
        }

        private static void SetCellValue(HSSFWorkbook workbook, ICell workCell, Type type, dynamic row, Cell cell)
        {
            var value = type.GetProperty(cell.Field).GetValue(row);
            if (value == null)
            {
                return;
            }

            if (value is DateTime)
            {
                workCell.SetCellValue((DateTime)value);
            }
            else if (value is int)
            {
                workCell.SetCellValue((int)value);
            }
            else if (value is double)
            {
                workCell.SetCellValue((double)value);
            }
            else
            {
                workCell.SetCellValue(value.ToString());
            }

            if (!string.IsNullOrWhiteSpace(cell.Format))
            {
                var cellStyle = workbook.CreateCellStyle();
                var format = workbook.CreateDataFormat();
                cellStyle.DataFormat = format.GetFormat(cell.Format);
                workCell.CellStyle = cellStyle;
            }
        }

        public void SetUp()
        {
            wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("new sheet");
            style = wb.CreateCellStyle();
            IDataFormat fmt = wb.CreateDataFormat();
            style.DataFormat=(fmt.GetFormat("hh:mm:ss"));

            cell11 = sheet.CreateRow(0).CreateCell(0);
            form = new DataFormatter();

            Evaluator = new HSSFFormulaEvaluator(wb);
        }

        private void btnExportToExcel_Click(object sender, RoutedEventArgs e)
        {
            SaveFileDialog sdfExport = new SaveFileDialog();
            sdfExport.Filter = "Excel文件|*.xls";
            if (sdfExport.ShowDialog() != true)
            {
                return;
            }
            string filename = sdfExport.FileName;
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("员工数据");

            IRow rowHeader = sheet.CreateRow(0);//表头行
            rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
            rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号");
            rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职日期");

            //把查询结果导出到Excel
            Employee[] employees = (Employee[])datagrid.ItemsSource;
            for (int i = 0; i < employees.Length; i++)
            {
                Employee employee = employees[i];
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
                row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);

                ICellStyle styledate = workbook.CreateCellStyle();
               IDataFormat format = workbook.CreateDataFormat();
            //格式具体有哪些请看单元格右键中的格式，有说明
               styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");

               ICell cellInDate = row.CreateCell(2, CellType.NUMERIC);
                cellInDate.CellStyle = styledate;
                cellInDate.SetCellValue(employee.InDate);
            }

            using (Stream stream = File.OpenWrite(filename))
            {
                workbook.Write(stream);
            }
        }

        public XlsModel(string sheetName)
        {
            SheetName = sheetName;

            // Create a new workbook and a sheet named "User Accounts"
            _workbook = new HSSFWorkbook();
            _sheet = _workbook.CreateSheet(SheetName);

            var boldFont = _workbook.CreateFont();
            boldFont.FontHeightInPoints = 10;
            boldFont.FontName = "Arial";
            boldFont.Boldweight = (short) FontBoldWeight.Bold;

            _dateStyle = _workbook.CreateCellStyle();
            _dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd/mm/yyyy");

            _nullStyle = _workbook.CreateCellStyle();
            _nullStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index;
            _nullStyle.FillPattern = FillPattern.SolidForeground;

            _headStyle = _workbook.CreateCellStyle();
            _headStyle.SetFont(boldFont);
        }

        public void TestCloneStyleDiffWB()
        {
            HSSFWorkbook wbOrig = new HSSFWorkbook();

            IFont fnt = wbOrig.CreateFont();
            fnt.FontName=("TestingFont");
            Assert.AreEqual(5, wbOrig.NumberOfFonts);

            IDataFormat fmt = wbOrig.CreateDataFormat();
            fmt.GetFormat("MadeUpOne");
            fmt.GetFormat("MadeUpTwo");

            NPOI.SS.UserModel.ICellStyle orig = wbOrig.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.Right);
            orig.SetFont(fnt);
            orig.DataFormat=(fmt.GetFormat("Test##"));

            Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
            Assert.AreEqual(fnt,orig.GetFont(wbOrig));
            Assert.AreEqual(fmt.GetFormat("Test##") , orig.DataFormat);

            // Now a style on another workbook
            HSSFWorkbook wbClone = new HSSFWorkbook();
            Assert.AreEqual(4, wbClone.NumberOfFonts);
            IDataFormat fmtClone = wbClone.CreateDataFormat();

            NPOI.SS.UserModel.ICellStyle clone = wbClone.CreateCellStyle();
            Assert.AreEqual(4, wbClone.NumberOfFonts);

            Assert.AreNotEqual(HorizontalAlignment.Right,clone.Alignment);
            Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName);

            clone.CloneStyleFrom(orig);
            Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreEqual("TestingFont" ,clone.GetFont(wbClone).FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"),clone.DataFormat);
            Assert.AreNotEqual(fmtClone.GetFormat("Test##") , fmt.GetFormat("Test##"));
            Assert.AreEqual(5, wbClone.NumberOfFonts);
        }

        public void TestHSSFCellToStringWithDataFormat()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);
            cell.SetCellValue(new DateTime(2009, 8, 20));
            NPOI.SS.UserModel.ICellStyle cellStyle = wb.CreateCellStyle();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
            cell.CellStyle = cellStyle;
            Assert.AreEqual("8/20/09", cell.ToString());

            NPOI.SS.UserModel.ICellStyle cellStyle2 = wb.CreateCellStyle();
            IDataFormat format = wb.CreateDataFormat();
            cellStyle2.DataFormat = format.GetFormat("YYYY-mm/dd");
            cell.CellStyle = cellStyle2;
            Assert.AreEqual("2009-08/20", cell.ToString());
        }

//.........这里部分代码省略.........
                        //layout = new TextLayout(str.GetEnumerator(), frc);
                        if (style.Rotation != 0)
                        {
                            /*
                             * Transform the text using a scale so that it's height is increased by a multiple of the leading,
                             * and then rotate the text before computing the bounds. The scale results in some whitespace around
                             * the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but
                             * is Added by the standard Excel autosize.
                             */
                            double angle = style.Rotation * 2.0 * Math.PI / 360.0;

                            //Tony Qu
                            //TODO:: text rotated width measure
                            //AffineTransform trans = new AffineTransform();
                            //trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
                            //trans.concatenate(
                            //AffineTransform.GetScaleInstance(1, fontHeightMultiple)
                            //);
                            width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
                            throw new NotImplementedException();
                        }
                        else
                        {
                            //width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
                            width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
                        }
                    }
                }
                else
                {
                    String sval = null;
                    if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                    {
                        NPOI.SS.UserModel.DataFormat dataformat = wb.CreateDataFormat();
                        short idx = style.DataFormat;
                        String format = "General";
                        if (idx >= 0)
                        {
                            format = dataformat.GetFormat(idx).Replace("\"", "");
                        }
                        double value = cell.NumericCellValue;
                        try
                        {
                            if ("General".Equals(format))
                                sval = "" + value;
                            else
                            {
                                sval = value.ToString("F");
                            }
                        }
                        catch (Exception)
                        {
                            sval = "" + value;
                        }
                    }
                    else if (cell.CellType == NPOI.SS.UserModel.CellType.BOOLEAN)
                    {
                        sval = cell.BooleanCellValue.ToString();
                    }
                    if (sval != null)
                    {
                        String txt = sval + defaultChar;
                        //str = new AttributedString(txt);
                        //copyAttributes(font, str, 0, txt.Length);

                        //layout = new TextLayout(str.GetEnumerator(), frc);

        public void ExportToExcel(int page, int size, int store, string mrf, string stockCode, string stockName, string status, string fd, string td, string enable)
        {
            // Get the data to report on
            var requisitionMasters = _service.ListCondition(page, size, store, mrf, stockCode, stockName, status,
                                                                 fd, td, enable);
            var requisitionDetails = _service.ListConditionDetailExcel(page, size, store, mrf, stockCode, stockName, status,
                                                                 fd, td, enable).OrderByDescending(i=>i.MRF);
            // Create a new workbook
            var workbook = new HSSFWorkbook();

            #region Cell Styles
            #region HeaderLabel Cell Style
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
            headerLabelCellStyle.SetFont(headerLabelFont);
            #endregion

            #region RightAligned Cell Style
            var rightAlignedCellStyle = workbook.CreateCellStyle();
            rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
            #endregion

            #region Currency Cell Style
            var currencyCellStyle = workbook.CreateCellStyle();
            currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
            var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
            if (formatId == -1)
            {
                var newDataFormat = workbook.CreateDataFormat();
                currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
            }
            else
                currencyCellStyle.DataFormat = formatId;
            #endregion

            #region Detail Subtotal Style
            var detailSubtotalCellStyle = workbook.CreateCellStyle();
            detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
            detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
            var detailSubtotalFont = workbook.CreateFont();
            detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
            detailSubtotalCellStyle.SetFont(detailSubtotalFont);
            #endregion

            #region Detail Currency Subtotal Style
            var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
            detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
            detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
            var detailCurrencySubtotalFont = workbook.CreateFont();
            detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
            detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
            formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
            if (formatId == -1)
            {
                var newDataFormat = workbook.CreateDataFormat();
                detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
            }
            else
                detailCurrencySubtotalCellStyle.DataFormat = formatId;
            #endregion
            #endregion

            #region Requisition sheet
            var sheet = workbook.CreateSheet("Requistion");

            // Add header labels
            var rowIndex = 0;

            // Undestand as row in excel. row + 3 = xuong 3 row.
            var row = sheet.CreateRow(rowIndex);
            var cell = row.CreateCell(0);
            cell.SetCellValue("MRF");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(1);
            cell.SetCellValue("Status");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(2);
            cell.SetCellValue("From");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(3);
            cell.SetCellValue("Deliver Date");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(4);
            cell.SetCellValue("Location");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(5);
            cell.SetCellValue("Project Code");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(6);
            cell.SetCellValue("Project Name");
            cell.CellStyle = headerLabelCellStyle;
//.........这里部分代码省略.........

        /// <summary>DataTable导出到Excel的MemoryStream</summary>
        static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            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 == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }
                    #region 表头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontName = str[5];                             //[5]
                        font.FontHeightInPoints = Convert.ToInt16(str[0]);  //[0]
                        font.Boldweight = Convert.ToInt16(str[1]);          //[1]
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();
                    }
                    #endregion
                    #region 列头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontName = str[6];                             //[6]
                        font.FontHeightInPoints = Convert.ToInt16(str[2]);  //[2]
                        font.Boldweight = Convert.ToInt16(str[3]);          //[3]
                        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) * Convert.ToInt32(str[4]));  //[4]
                        }
                        //headerRow.Dispose();
                    }
                    #endregion
                    rowIndex = 2;
                }
                #endregion
                #region 填充内容
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String": //字符串类型
                            string result = drValue;
                            newCell.SetCellValue(result);
                            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);
//.........这里部分代码省略.........

        /// </summary>
        /// <param name="dt"> 数据源</param>
        /// <returns>stream</returns>
        public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = null;

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

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

            sheet = (HSSFSheet)workbook.CreateSheet(sheetname);
            #region 表头及样式
            {
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(strHeaderText);

                HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = (HSSFFont)workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new Region(0, 0, 0, dt.Columns.Count - 1));
                headerRow = null;
                //headerRow.Dispose();
            }
            #endregion

            #region 列头及样式
            {
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
                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 dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable，将datatable的列名赋予sheet
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

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

            int index = 2; //表头和列头已经占用一行，所以从2开始
            foreach (DataRow row in dt.Rows)
            {
                HSSFRow datarow = (HSSFRow)sheet.CreateRow(index);

                foreach (DataColumn column in dt.Columns)
                {
                    // row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet

                    HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); //　　实例化cell

                    string drValue = row[column].ToString();
                    if (drValue == null || drValue == "")
                    {
                        newCell.SetCellValue("");
                        continue;
                    }

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                        case "System.DateTime"://日期类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
//.........这里部分代码省略.........

        public void TestWriteDataFormat()
        {
            string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls");
            FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = null;
            ICell c = null;
            IDataFormat format = wb.CreateDataFormat();
            NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();

            short df = format.GetFormat("0.0");
            cs.DataFormat=(df);

            r = s.CreateRow(0);
            c = r.CreateCell(0);
            c.CellStyle=(cs);
            c.SetCellValue(1.25);

            wb.Write(out1);
            out1.Close();

            FileStream stream = new FileStream(filepath,FileMode.OpenOrCreate);
            POIFSFileSystem fs = new POIFSFileSystem(stream);
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
            ICell cell =
                             sheet.GetRow(0).GetCell(0);
            format = workbook.CreateDataFormat();

            Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10);

            Assert.AreEqual(format.GetFormat(df), "0.0");

            Assert.AreEqual(format, workbook.CreateDataFormat());

            stream.Close();
        }

        private void btnExportToExcel_Click(object sender, RoutedEventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "excel文件|*.xls";
            if (sfd.ShowDialog() != true)
            {
                return;
            }
            string fileName = sfd.FileName;
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("员工信息表");
            IRow rowHeader = sheet.CreateRow(0);
            rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
            rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号");
            rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职时间");
            rowHeader.CreateCell(3, CellType.STRING).SetCellValue("学历");
            rowHeader.CreateCell(4, CellType.STRING).SetCellValue("毕业院校");
            rowHeader.CreateCell(5, CellType.STRING).SetCellValue("基本工资");
            rowHeader.CreateCell(6, CellType.STRING).SetCellValue("部门");
            rowHeader.CreateCell(7, CellType.STRING).SetCellValue("职位");
            rowHeader.CreateCell(8, CellType.STRING).SetCellValue("合同签订日期");
            rowHeader.CreateCell(9, CellType.STRING).SetCellValue("合同到期日期");

            Employee[] employees = (Employee[])datagrid.ItemsSource;

            ICellStyle cellStyle = workbook.CreateCellStyle();
            IDataFormat dataFormat = workbook.CreateDataFormat();

            cellStyle.DataFormat = dataFormat.GetFormat("yyyy\"年\"m\"月\"d\"日\"");

            for (int i = 0; i < employees.Length; i++)
            {
                Employee employee = employees[i];
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
                row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);
                ICell dateCell = row.CreateCell(2, CellType.NUMERIC);
                dateCell.CellStyle = cellStyle;
                dateCell.SetCellValue(employee.InDate);

                row.CreateCell(3, CellType.STRING).SetCellValue(IdNameDAL.GetEducationNameById(employee.EducationId));
                row.CreateCell(4, CellType.STRING).SetCellValue(employee.School);
                row.CreateCell(5, CellType.STRING).SetCellValue(employee.BaseSalary);
                row.CreateCell(6, CellType.STRING).SetCellValue(DepartmentDAL.GetById(employee.DepartmentId).Name);
                row.CreateCell(7, CellType.STRING).SetCellValue(employee.Position);
                ICell contractBegindateCell = row.CreateCell(8, CellType.NUMERIC);
                contractBegindateCell.CellStyle = cellStyle;
                contractBegindateCell.SetCellValue(employee.ContractStartDay);

                ICell contractEnddateCell = row.CreateCell(9, CellType.NUMERIC);
                contractEnddateCell.CellStyle = cellStyle;
                contractEnddateCell.SetCellValue(employee.ContractEndDay);
                
            }

            using (Stream stream = File.OpenWrite(fileName))
            {
                workbook.Write(stream);
            }

        }

        public void ExportToExcel(int page, int size, int store, int poType, string status, string po, int supplier, string srv, string stockCode, string stockName, string fd, string td, string enable)
        {
            // Get the data to report on
            var masters = _service.ListCondition(page, size, store, poType, status, po, supplier, srv, stockCode, stockName, fd, td, enable);
            var details = _service.ListConditionDetailExcel(page, size, store, poType, status, po, supplier, srv, stockCode, stockName, fd, td, enable);
            // Create a new workbook
            var workbook = new HSSFWorkbook();

            #region Cell Styles
            #region HeaderLabel Cell Style
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
            headerLabelCellStyle.SetFont(headerLabelFont);
            #endregion

            #region RightAligned Cell Style
            var rightAlignedCellStyle = workbook.CreateCellStyle();
            rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
            #endregion

            #region Currency Cell Style
            var currencyCellStyle = workbook.CreateCellStyle();
            currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
            var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
            if (formatId == -1)
            {
                var newDataFormat = workbook.CreateDataFormat();
                currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
            }
            else
                currencyCellStyle.DataFormat = formatId;
            #endregion

            #region Detail Subtotal Style
            var detailSubtotalCellStyle = workbook.CreateCellStyle();
            detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
            detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
            var detailSubtotalFont = workbook.CreateFont();
            detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
            detailSubtotalCellStyle.SetFont(detailSubtotalFont);
            #endregion

            #region Detail Currency Subtotal Style
            var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
            detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
            detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
            var detailCurrencySubtotalFont = workbook.CreateFont();
            detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
            detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
            formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
            if (formatId == -1)
            {
                var newDataFormat = workbook.CreateDataFormat();
                detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
            }
            else
                detailCurrencySubtotalCellStyle.DataFormat = formatId;
            #endregion
            #endregion

            #region Master sheet
            var sheet = workbook.CreateSheet("Main");

            // Add header labels
            var rowIndex = 0;

            // Undestand as row in excel. row + 3 = xuong 3 row.
            var row = sheet.CreateRow(rowIndex);
            var cell = row.CreateCell(0);
            cell.SetCellValue("No");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(1);
            cell.SetCellValue("PE Code");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(2);
            cell.SetCellValue("Supplier");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(3);
            cell.SetCellValue("PE Date");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(4);
            cell.SetCellValue("PE Total");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(5);
            cell.SetCellValue("Location");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(6);
            cell.SetCellValue("PE Type");
            cell.CellStyle = headerLabelCellStyle;

            cell = row.CreateCell(7);
//.........这里部分代码省略.........

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)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 = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            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 = (HSSFSheet)workbook.CreateSheet();
                    }

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

                        HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = (HSSFFont)workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.
                    }
                    #endregion


                    #region 列头及样式
                    {
                        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
                        HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.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);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

//.........这里部分代码省略.........