C# XSSFWorkbook.CreateFont方法代码示例

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;

            #region 右击文件 属性信息

            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "http://www.yongfa365.com/";
            //    workbook.DocumentSummaryInformation = dsi;

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

            #endregion

            XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
            XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
            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)
                {
                    #region 表头及样式
                    //{
                    //    XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                    //    headerRow.HeightInPoints = 25;
                    //    headerRow.CreateCell(0).SetCellValue(strHeaderText);

                    //    XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                    //    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    //    XSSFFont font = workbook.CreateFont() as XSSFFont;
                    //    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.Dispose();
                    //}

                    #endregion


                    #region 列头及样式

                    {
                        XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;


                        XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        XSSFFont font = workbook.CreateFont() as XSSFFont;
                        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;

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

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            //font style1: underlined, italic, red color, fontsize=20
            IFont font1 = workbook.CreateFont();
            font1.Color = IndexedColors.Red.Index;
            font1.IsItalic = true;
            font1.Underline = FontUnderlineType.Double;
            font1.FontHeightInPoints = 20;

            //bind font with style 1
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);

            //font style2: strikeout line, green color, fontsize=15, fontname='宋体'
            IFont font2 = workbook.CreateFont();
            font2.Color = IndexedColors.OliveGreen.Index;
            font2.IsStrikeout = true;
            font2.FontHeightInPoints = 15;
            font2.FontName = "宋体";

            //bind font with style 2
            ICellStyle style2 = workbook.CreateCellStyle();
            style2.SetFont(font2);

            //apply font styles
            ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
            cell1.SetCellValue("Hello World!");
            cell1.CellStyle = style1;
            ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
            cell2.SetCellValue("早上好！");
            cell2.CellStyle = style2;

            ////cell with rich text 
            ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
            XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");

            //apply font to "Microsoft Office"
            IFont font4 = workbook.CreateFont();
            font4.FontHeightInPoints = 12;
            richtext.ApplyFont(0, 16, font4);
            //apply font to "TM"
            IFont font3 = workbook.CreateFont();
            font3.TypeOffset = FontSuperScript.Super;
            font3.IsItalic = true;
            font3.Color = IndexedColors.Blue.Index;
            font3.FontHeightInPoints = 8;
            richtext.ApplyFont(16, 18, font3);

            cell3.SetCellValue(richtext);

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            // Create a Worksheet
            ISheet ws = wb.CreateSheet("Sheet1");

            // Create a new font and alter it
            IFont font = wb.CreateFont();
            font.FontHeightInPoints = 24;
            font.FontName = "Courier New";
            font.IsItalic = true;
            font.IsStrikeout = true;            

            // Fonts are set into a style so create a new one to use.
            ICellStyle style = wb.CreateCellStyle();
            style.SetFont(font);

            IRow row = ws.CreateRow(0);

            // Create a cell and put a value in it.
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("Thisi s a test of fonts");
            cell.CellStyle = style;

            FileStream sw = File.Create("test.xlsx");
            wb.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ////cell style for hyperlinks
            ////by default hyperlinks are blue and underlined
            ICellStyle hlink_style = workbook.CreateCellStyle();
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
            hlink_font.Color = HSSFColor.BLUE.index;
            hlink_style.SetFont(hlink_font);

            ICell cell;
            ISheet sheet = workbook.CreateSheet("Hyperlinks");

            //URL
            cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue("URL Link");
            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
            link.Address = ("http://poi.apache.org/");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //link to a file in the current directory
            cell = sheet.CreateRow(1).CreateCell(0);
            cell.SetCellValue("File Link");
            link = new XSSFHyperlink(HyperlinkType.FILE);
            link.Address = ("link1.xls");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //e-mail link
            cell = sheet.CreateRow(2).CreateCell(0);
            cell.SetCellValue("Email Link");
            link = new XSSFHyperlink(HyperlinkType.EMAIL);
            //note, if subject contains white spaces, make sure they are url-encoded
            link.Address = ("mailto:[email protected]?subject=Hyperlinks");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //link to a place in this workbook

            //Create a target sheet and cell
            ISheet sheet2 = workbook.CreateSheet("Target ISheet");
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");

            cell = sheet.CreateRow(3).CreateCell(0);
            cell.SetCellValue("Worksheet Link");
            link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
            link.Address = ("'Target ISheet'!A1");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        protected void btn_click(object sender, EventArgs e)
        {
            //FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read);
            //XSSFWorkbook temWorkBook = new XSSFWorkbook(fs);
            //ISheet nsheet = temWorkBook.GetSheet("Sheet1");
            //IRow datarow = nsheet.GetRow(4);

            //datarow.GetCell(0).SetCellValue(77);
            //nsheet.ForceFormulaRecalculation = true;

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

            //    Response.Clear();
            //    Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //    Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx");
            //    Response.BinaryWrite(ms.ToArray());
            //    Response.End();
            //}

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("New Sheet");
            ISheet sheet2 = workbook.CreateSheet("Second Sheet");
            ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
            IFont fontBold = workbook.CreateFont();
            fontBold.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(fontBold);
            cell1.CellStyle = style1;
            cell1.SetCellValue("sample value");
            int x = 1;
            for (int i = 1; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                for(int j = 0;j < 15;j++)
                {
                    row.CreateCell(j).SetCellValue(x++);
                }
            }
            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);

                Response.Clear();
                Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx");
                Response.BinaryWrite(ms.ToArray());
                Response.End();
            }
        }

        public void TestCloneStyleSameWB()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            Assert.AreEqual(1, wb.NumberOfFonts);

            XSSFFont fnt = (XSSFFont)wb.CreateFont();
            fnt.FontName = ("TestingFont");
            Assert.AreEqual(2, wb.NumberOfFonts);

            XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.RIGHT);
            orig.SetFont(fnt);
            orig.DataFormat = (short)18;

            Assert.AreEqual(HorizontalAlignment.RIGHT,orig.Alignment);
            Assert.AreEqual(fnt,orig.GetFont());
            Assert.AreEqual(18,orig.DataFormat);

            XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
            Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
            Assert.AreNotEqual(fnt,clone.GetFont());
            Assert.AreNotEqual(18,clone.DataFormat);

            clone.CloneStyleFrom(orig);
            Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
            Assert.AreEqual(fnt, clone.GetFont());
            Assert.AreEqual(18, clone.DataFormat);
            Assert.AreEqual(2, wb.NumberOfFonts);
        }

        public static byte[] GenerateTemplate(List<Business.Entities.company> listCompany,List<Business.Entities.contractor> listContractor,List<Business.Entities.project> listProject)
        {
            //culture
            Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
            int parseRecordNumber = 100; // number of rows that has style or validation
            int startRowIndex = 3;

            XSSFCellStyle styleCurrency;
            XSSFCellStyle styleDate;
            XSSFCellStyle styleNumeric;
            XSSFCellStyle styleDecimal;

            //kamus
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet; XSSFRow row; XSSFCell cell;

            XSSFCellStyle style; XSSFFont font;

            CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation;

            List<string> listCompanyString = new List<string>();
              foreach(var data in  listCompany)
              {
            listCompanyString.Add(data.name);
              }

            List<string> listContractorString = new List<string>();
              foreach(var data in  listContractor)
              {
            listContractorString.Add(data.name);
              }

            List<string> listProjectString = new List<string>();
              foreach(var data in  listProject)
              {
            listProjectString.Add(data.name);
              }

            styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
            styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");

            styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle();
            styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");

            styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
            styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy");

                     styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle();
            styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");

            List<string> columnList = new List<string>();
                            columnList.Add("Name");
               			int ContractorStringLocation = 1;
            columnList.Add("Contractor");
                        columnList.Add("Photo");
               			columnList.Add("Description");
               			columnList.Add("Start Date");
               			columnList.Add("Finish Date");
               			columnList.Add("Highlight");
               			columnList.Add("Project Stage");
               			columnList.Add("Status");
               			columnList.Add("Budget");
               			columnList.Add("Currency");
               			columnList.Add("Num");
               			int PmcStringLocation = 12;
            columnList.Add("Pmc");
                        columnList.Add("Summary");
               			int CompanyStringLocation = 14;
            columnList.Add("Company");
                        columnList.Add("Status Non Technical");
               			columnList.Add("Is Completed");
               			columnList.Add("Completed Date");
               			int ProjectStringLocation = 18;
            columnList.Add("Project");
                        columnList.Add("Submit For Approval Time");
               			columnList.Add("Approval Status");
               			columnList.Add("Approval Time");
               			columnList.Add("Deleted");
               			columnList.Add("Approval Message");
               			columnList.Add("Status Technical");
               			columnList.Add("Scurve Data");

            sheet = (XSSFSheet)workbook.CreateSheet("Data");
            int col = 0;
            int rowNumber = 0;
            //create row (header)
            row = (XSSFRow)sheet.CreateRow((short)rowNumber);
            dvHelper = new XSSFDataValidationHelper(sheet);
            //header data
            style = (XSSFCellStyle)workbook.CreateCellStyle();
            cell = (XSSFCell)row.CreateCell(col);
            cell.SetCellValue("M Project");
            font = (XSSFFont)workbook.CreateFont();
            font.FontHeight = 24;
            style.SetFont(font);
            cell.CellStyle = style;
            rowNumber++;
            row = (XSSFRow)sheet.CreateRow((short)rowNumber);

            style = (XSSFCellStyle)workbook.CreateCellStyle();
//.........这里部分代码省略.........

        public void TestCloneStyleDiffWB()
        {
            XSSFWorkbook wbOrig = new XSSFWorkbook();
            Assert.AreEqual(1, wbOrig.NumberOfFonts);
            Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);

            XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
            fnt.FontName = ("TestingFont");
            Assert.AreEqual(2, wbOrig.NumberOfFonts);
            Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);

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

            XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.Right);
            orig.SetFont(fnt);
            orig.DataFormat = (fmt.GetFormat("Test##"));

            Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
            Assert.IsTrue(fnt == orig.GetFont());
            Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);

            Assert.AreEqual(2, wbOrig.NumberOfFonts);
            Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);


            // Now a style on another workbook
            XSSFWorkbook wbClone = new XSSFWorkbook();
            Assert.AreEqual(1, wbClone.NumberOfFonts);
            Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
            Assert.AreEqual(1, wbClone.NumCellStyles);

            XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
            XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();

            Assert.AreEqual(1, wbClone.NumberOfFonts);
            Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);

            Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
            Assert.IsFalse("TestingFont" == clone.GetFont().FontName);

            clone.CloneStyleFrom(orig);

            Assert.AreEqual(2, wbClone.NumberOfFonts);
            Assert.AreEqual(2, wbClone.NumCellStyles);
            Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);

            Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreEqual("TestingFont", clone.GetFont().FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
            Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));

            // Save it and re-check
            XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
            Assert.AreEqual(2, wbReload.NumberOfFonts);
            Assert.AreEqual(2, wbReload.NumCellStyles);
            Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);

            XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
            Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
            Assert.AreEqual("TestingFont", reload.GetFont().FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
            Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
        }

        public void TestNew()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
            //multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
            XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
            XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
            Assert.AreSame(dr1, dr2);

            List<POIXMLDocumentPart> rels = sheet.GetRelations();
            Assert.AreEqual(1, rels.Count);
            Assert.IsTrue(rels[0] is XSSFDrawing);

            XSSFDrawing drawing = (XSSFDrawing)rels[0];
            String drawingId = drawing.GetPackageRelationship().Id;

            //there should be a relation to this Drawing in the worksheet
            Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
            Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);

            //XSSFClientAnchor anchor = new XSSFClientAnchor();

            XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
            c1.LineWidth = 2.5;
            c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;

            XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));

            XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
            c3.SetText(new XSSFRichTextString("Test String"));
            c3.SetFillColor(128, 128, 128);

            XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
            XSSFRichTextString rt = new XSSFRichTextString("Test String");
            rt.ApplyFont(0, 5, wb.CreateFont());
            rt.ApplyFont(5, 6, wb.CreateFont());
            c4.SetText(rt);

            c4.IsNoFill = (true);

            Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());

            List<XSSFShape> shapes = drawing.GetShapes();
            Assert.AreEqual(4, shapes.Count);
            Assert.IsTrue(shapes[(0)] is XSSFConnector);
            Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
            Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
            Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);

            // Save and re-load it
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            sheet = wb.GetSheetAt(0) as XSSFSheet;

            // Check
            dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
            CT_Drawing ctDrawing = dr1.GetCTDrawing();

            // Connector, shapes and text boxes are all two cell anchors
            Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
            Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
            Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());

            shapes = dr1.GetShapes();
            Assert.AreEqual(4, shapes.Count);
            Assert.IsTrue(shapes[0] is XSSFConnector);
            Assert.IsTrue(shapes[1] is XSSFShapeGroup);
            Assert.IsTrue(shapes[2] is XSSFSimpleShape);
            Assert.IsTrue(shapes[3] is XSSFSimpleShape); //

            // Ensure it got the right namespaces
            //String xml = ctDrawing.ToString();
            //Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
            //Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
        }

        internal void createExcelReport(string filePath, string carRegNo, string organizationName, List<DriveHistoryView> recordList)
        {
            try
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Drive Record");

                int rowIndex = 0;
                IRow row = sheet.CreateRow(rowIndex);

                ICell cell = row.CreateCell(0);
                IFont font = workbook.CreateFont();
                font.Boldweight = (short)FontBoldWeight.Bold;
                cell.SetCellValue("Doctor Car Drive Operation Management System Report - Drive Records");
                cell.CellStyle.SetFont(font);
                CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 4);

                //cell = row.CreateCell(5);
                //cell.SetCellValue(record.StartDate.ToString());
                //CellManager.columnMerge(sheet, rowIndex, 5, 6, false);

                rowIndex++;
                sheet.CreateRow(rowIndex);

                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                cell = CellManager.CreateCell(workbook, row, 1, "Car Reg No", false);
                CellStyle.GrayBackground(workbook, cell, false);
                cell = CellManager.CreateCell(workbook, row, 2, carRegNo, false);
                CellManager.topBottomBorder(workbook, cell, row, 1);
                CellManager.columnMerge(sheet, rowIndex, 2, 3, true);

                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = CellManager.CreateCell(workbook, row, 1, "Organization", false);
                CellStyle.GrayBackground(workbook, cell, false);
                cell = CellManager.CreateCell(workbook, row, 2, organizationName.Trim(), false);
                CellManager.topBottomBorder(workbook, cell, row, 1);
                CellManager.columnMerge(sheet, rowIndex, 2, 3, true);

                rowIndex++;
                sheet.CreateRow(rowIndex);

                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(1);
                cell.SetCellValue("Drive Details");
                CellManager.topBottomBorder(workbook, cell, row, 11);
                CellManager.columnMerge(sheet, rowIndex, 1, 12, true);
                CellStyle.GrayBackground(workbook, cell, true);

                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = CellManager.CreateCell(workbook, row, 1, "#", true);
                cell = CellManager.CreateCell(workbook, row, 2, "Start Date time", true);
                cell = CellManager.CreateCell(workbook, row, 3, "End Date Time", true);
                cell = CellManager.CreateCell(workbook, row, 4, "Operation Time (h)", true);
                cell = CellManager.CreateCell(workbook, row, 5, "Start Odometer (km)", true);
                cell = CellManager.CreateCell(workbook, row, 6, "End Odometer (km)", true);
                cell = CellManager.CreateCell(workbook, row, 7, "Distance (km)", true);
                cell = CellManager.CreateCell(workbook, row, 8, "Velocity (km/h)", true);
                cell = CellManager.CreateCell(workbook, row, 9, "Drive From", true);
                cell = CellManager.CreateCell(workbook, row, 10, "Drive To", true);
                cell = CellManager.CreateCell(workbook, row, 11, "Driver", true);
                cell = CellManager.CreateCell(workbook, row, 12, "No. Patients", true);

                int recordNo = 1;
                foreach (DriveHistoryView record in recordList)
                {

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    cell = CellManager.CreateCell(workbook, row, 1, recordNo, true);
                    cell = CellManager.CreateCell(workbook, row, 2, record.StartDate.ToString(), false);
                    cell = CellManager.CreateCell(workbook, row, 3, record.EndDate.ToString(), false);
                    TimeSpan duration = ((DateTime)record.EndDate - (DateTime)record.StartDate);
                    cell = CellManager.CreateCell(workbook, row, 4, duration.Hours, false);
                    cell = CellManager.CreateCell(workbook, row, 5, record.StartOdometer, false);
                    cell = CellManager.CreateCell(workbook, row, 6, record.EndOdometer, false);
                    cell = CellManager.CreateCell(workbook, row, 7, (record.EndOdometer - record.StartOdometer), false);
                    if (duration.Hours > 0)
                    {
                        cell = CellManager.CreateCell(workbook, row, 8, Math.Round((double)(record.EndOdometer - record.StartOdometer) / duration.Hours, 1), false);
                    }
                    else
                    {
                        cell = CellManager.CreateCell(workbook, row, 8, "", false);
                    }
                    cell = CellManager.CreateCell(workbook, row, 9, record.From.ToString(), false);
                    cell = CellManager.CreateCell(workbook, row, 10, record.To.ToString(), false);
                    cell = CellManager.CreateCell(workbook, row, 11, record.DriverName.ToString(), false);
                    cell = CellManager.CreateCell(workbook, row, 12, record.NumPatients, false);

                    recordNo++;

                    for (int i = 0; i < 15; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }
//.........这里部分代码省略.........

        public void TestSetString()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
            XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());

            //passing HSSFRichTextString is incorrect
            try
            {
                comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
            }

            //simple string argument
            comment.SetString(TEST_RICHTEXTSTRING);
            Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);

            //if the text is already Set, it should be overridden, not Added twice!
            comment.SetString(TEST_RICHTEXTSTRING);

            CT_Comment ctComment = comment.GetCTComment();
          //  Assert.Fail("TODO test case incomplete!?");
            //XmlObject[] obj = ctComment.selectPath(
            //        "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
            //Assert.AreEqual(1, obj.Length);
            Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);

            //sequential call of comment.String should return the same XSSFRichTextString object
            Assert.AreSame(comment.String, comment.String);

            XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
            XSSFFont font1 = (XSSFFont)wb.CreateFont();
            font1.FontName = ("Tahoma");
            //font1.FontHeight = (short)8.5;
            font1.SetFontHeight(8.5);
            font1.IsItalic = true;
            font1.Color = IndexedColors.BlueGrey.Index;
            richText.ApplyFont(0, 5, font1);

            //check the low-level stuff
            comment.String = richText;
            //obj = ctComment.selectPath(
            //        "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
            //Assert.AreEqual(1, obj.Length);
            Assert.AreSame(comment.String, richText);
            //check that the rich text is Set in the comment
            CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
            Assert.AreEqual(true, rPr.GetIArray(0).val);
            Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
            Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
            Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
        }

        public static void ExportHandleNumExcel(Dictionary<string, Model.DTO.HKHandleNumDetail> hkHandleNumDetails, DateTime dt, ref string fileName)
        {
            FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号6 香港新马寄出个数量化单（5.5之后）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            ISheet sheet = workbook.GetSheet("香港新马");

            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            style.SetFont(font);

            IRow row = sheet.GetRow(1);
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("英亚网申3");
            cell.CellStyle = style;
            cell = row.CreateCell(6);
            cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
            cell.CellStyle = style;
            cell = row.CreateCell(10);
            cell.SetCellValue(hkHandleNumDetails.Count + "个");
            cell.CellStyle = style;

            //设置单元格格式
            style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            style.SetFont(font);

            //方框样式
            ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
            trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
            trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
            trueOrFalseStyle.BorderTop = BorderStyle.Thin;
            trueOrFalseStyle.BorderRight = BorderStyle.Thin;
            trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
            trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
            font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 12;
            trueOrFalseStyle.SetFont(font);
            int i = 0;
            //for (int i = 0; i < ukHandleNumDetails.Count; i++)
            //{
            foreach (Model.DTO.HKHandleNumDetail item in hkHandleNumDetails.Values)
            {
                row = sheet.CreateRow(4 + i);
                row.HeightInPoints = 25;
                //序号
                cell = row.CreateCell(0);
                cell.CellStyle = style;
                cell.SetCellValue(i + 1);
                //合同号
                cell = row.CreateCell(1);
                cell.CellStyle = style;
                cell.SetCellValue(item.ContractNum);
                //学生姓名
                cell = row.CreateCell(2);
                cell.CellStyle = style;
                cell.SetCellValue(item.StudentName);
                //申请学历
                cell = row.CreateCell(3);
                cell.CellStyle = style;
                cell.SetCellValue(item.Education);
                //学校申请
                cell = row.CreateCell(4);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.Application);
                //新加坡公立硕士
                cell = row.CreateCell(5);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.HK.SingaporeMaster);
                //香港博士
                cell = row.CreateCell(6);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.HK.Doctor);
                //签证申请
                cell = row.CreateCell(7);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.Visa);
                //资深文案
                cell = row.CreateCell(8);
                cell.CellStyle = style;
                cell.SetCellValue(item.CopyWriting.Senior);
                //制作文案
                cell = row.CreateCell(9);
                cell.CellStyle = style;
                cell.SetCellValue(item.CopyWriting.Author);
                //寄出日期
                cell = row.CreateCell(10);
                cell.CellStyle = style;
//.........这里部分代码省略.........

        public void XSSFTextParagraph_()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
                XSSFRichTextString rt = new XSSFRichTextString("Test String");

                XSSFFont font = wb.CreateFont() as XSSFFont;
                Color color = Color.FromArgb(0, 255, 255);
                font.SetColor(new XSSFColor(color));
                font.FontName = (/*setter*/"Arial");
                rt.ApplyFont(font);

                shape.SetText(rt);

                List<XSSFTextParagraph> paras = shape.TextParagraphs;
                Assert.AreEqual(1, paras.Count);

                XSSFTextParagraph text = paras[(0)];
                Assert.AreEqual("Test String", text.Text);

                Assert.IsFalse(text.IsBullet);
                Assert.IsNotNull(text.GetXmlObject());
                Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
                Assert.IsNotNull(text.GetEnumerator());
                Assert.IsNotNull(text.AddLineBreak());

                Assert.IsNotNull(text.TextRuns);
                Assert.AreEqual(2, text.TextRuns.Count);
                text.AddNewTextRun();
                Assert.AreEqual(3, text.TextRuns.Count);

                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = (/*setter*/TextAlign.CENTER);
                Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
                text.TextAlign = (/*setter*/TextAlign.RIGHT);
                Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);

                text.TextFontAlign = (/*setter*/TextFontAlign.BASELINE);
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = (/*setter*/TextFontAlign.BOTTOM);
                Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);

                Assert.IsNull(text.BulletFont);
                text.BulletFont = (/*setter*/"Arial");
                Assert.AreEqual("Arial", text.BulletFont);

                Assert.IsNull(text.BulletCharacter);
                text.BulletCharacter = (/*setter*/".");
                Assert.AreEqual(".", text.BulletCharacter);

                //Assert.IsNull(text.BulletFontColor);
                Assert.AreEqual(Color.Empty, text.BulletFontColor);
                text.BulletFontColor = (/*setter*/color);
                Assert.AreEqual(color, text.BulletFontColor);

                Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);

                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (/*setter*/2.0);
                Assert.AreEqual(2.0, text.Indent, 0.01);
                text.Indent = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);

                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/3.0);
                Assert.AreEqual(3.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);

                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/4.5);
//.........这里部分代码省略.........

        private IWorkbook GenerateHeader()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheetReport = workbook.CreateSheet("Report");
            IFont fontBold = workbook.CreateFont();
            fontBold.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle styleFontBold = workbook.CreateCellStyle();
            styleFontBold.SetFont(fontBold);
            ICell cell1 = sheetReport.CreateRow(0).CreateCell(0);
            cell1.CellStyle = styleFontBold;
            cell1.SetCellValue("From " + txtBoxFrom.Text + " to " + txtBoxTo.Text);
            sheetReport.CreateRow(1).CreateCell(0).SetCellValue("Labor Cost Report");
            sheetReport.GetRow(1).GetCell(0).CellStyle = styleFontBold;

            return workbook;
        }

        public void Test48877()
        {
            String text = "Use \n with word wrap on to create a new line.\n" +
               "This line finishes with two trailing spaces.  ";

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;

            IFont font1 = wb.CreateFont();
            font1.Color=((short)20);
            IFont font2 = wb.CreateFont();
            font2.Color = (short)(FontColor.RED);
            IFont font3 = wb.GetFontAt((short)0);

            XSSFRow row = sheet.CreateRow(2) as XSSFRow;
            XSSFCell cell = row.CreateCell(2) as XSSFCell;

            XSSFRichTextString richTextString =
               wb.GetCreationHelper().CreateRichTextString(text) as XSSFRichTextString;

            // Check the text has the newline
            Assert.AreEqual(text, richTextString.String);

            // Apply the font
            richTextString.ApplyFont(font3);
            richTextString.ApplyFont(0, 3, font1);
            cell.SetCellValue(richTextString);

            // To enable newlines you need Set a cell styles with wrap=true
            ICellStyle cs = wb.CreateCellStyle();
            cs.WrapText=(true);
            cell.CellStyle=(cs);

            // Check the text has the
            Assert.AreEqual(text, cell.StringCellValue);

            // Save the file and re-read it
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            sheet = wb.GetSheetAt(0) as XSSFSheet;
            row = sheet.GetRow(2) as XSSFRow;
            cell = row.GetCell(2) as XSSFCell;
            Assert.AreEqual(text, cell.StringCellValue);

            // Now add a 2nd, and check again
            int fontAt = text.IndexOf("\n", 6);
            cell.RichStringCellValue.ApplyFont(10, fontAt + 1, font2);
            Assert.AreEqual(text, cell.StringCellValue);

            Assert.AreEqual(4, (cell.RichStringCellValue as XSSFRichTextString).NumFormattingRuns);
            Assert.AreEqual("Use", (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[0].t);

            String r3 = (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[2].t;
            Assert.AreEqual("line.\n", r3.Substring(r3.Length - 6));

            // Save and re-check
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            sheet = wb.GetSheetAt(0) as XSSFSheet;
            row = sheet.GetRow(2) as XSSFRow;
            cell = row.GetCell(2) as XSSFCell;
            Assert.AreEqual(text, cell.StringCellValue);

            //       FileOutputStream out = new FileOutputStream("/tmp/test48877.xlsx");
            //       wb.Write(out);
            //       out.Close();
        }