Ejemplo n.º 1
0
        public void Test40520_3()
        {
            short font           = 3;
            HSSFRichTextString r = new HSSFRichTextString("f0_123456789012345678901234567890123456789012345678901234567890");

            // wrong order => will format 0-6
            r.ApplyFont(0, 2, font);
            r.ApplyFont(5, 7, font);
            r.ApplyFont(0, 2, font);

            r.ApplyFont(0, 2, font);
            for (int i = 0; i < 2; i++)
            {
                Assert.AreEqual(font, r.GetFontAtIndex(i));
            }
            for (int i = 2; i < 5; i++)
            {
                Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
            }
            for (int i = 5; i < 7; i++)
            {
                Assert.AreEqual(font, r.GetFontAtIndex(i));
            }
            for (int i = 7; i < r.Length; i++)
            {
                Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 对单元格赋值,设置时间下划线
        /// </summary>
        /// <param name="workbook">需要添加下划线的工作簿</param>
        /// <param name="row">单元格行号</param>
        /// <param name="row">单元格列号</param>
        /// <param name="cell"></param>
        /// <param name="vaule"></param>
        /// <param name="startTime">起始日期</param>
        /// <param name="endTime">截止日期</param>
        public static void SetDateTimeUnderline(this IWorkbook workbook, int row, int cell, string vaule, DateTime startTime, DateTime endTime)
        {
            string startYear  = startTime.Year.ToString();
            string startMonth = startTime.Month.ToString();
            string startDay   = startTime.Day.ToString();
            string endYear    = endTime.Year.ToString();
            string endMonth   = endTime.Month.ToString();
            string endDay     = endTime.Day.ToString();

            string strValue = $"{startYear}年{startMonth}月{startDay}日至{endYear}年{endMonth}月{endDay}日";

            HSSFRichTextString richtext = new HSSFRichTextString(strValue);

            ApplyFont(workbook, richtext, 11);

            var font2         = SetUnderline(workbook, richtext, 11, 0, 4);
            int monthEndIndex = 5 + startMonth.Length;

            richtext.ApplyFont(5, monthEndIndex, font2);
            richtext.ApplyFont(monthEndIndex + 1, monthEndIndex + 1 + startDay.Length, font2);
            richtext.ApplyFont(monthEndIndex + 1 + startDay.Length + 2, monthEndIndex + 1 + startDay.Length + 6, font2);

            int monthEndIndex2 = monthEndIndex + 1 + startDay.Length + 7;

            richtext.ApplyFont(monthEndIndex2, monthEndIndex2 + endMonth.Length, font2);
            richtext.ApplyFont(monthEndIndex2 + endMonth.Length + 1, monthEndIndex2 + endMonth.Length + 1 + endDay.Length, font2);

            ISheet sheet = workbook.GetSheetAt(0);

            sheet.GetRow(row).GetCell(cell).SetCellValue(richtext);
        }
Ejemplo n.º 3
0
        public void Test40520_2()
        {
            short font           = 3;
            HSSFRichTextString r = new HSSFRichTextString("f0_123456789012345678901234567890123456789012345678901234567890");

            r.ApplyFont(0, 2, font);
            for (int i = 0; i < 2; i++)
            {
                Assert.AreEqual(font, r.GetFontAtIndex(i));
            }
            for (int i = 2; i < r.Length; i++)
            {
                Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
            }

            r.ApplyFont(0, 2, font);
            for (int i = 0; i < 2; i++)
            {
                Assert.AreEqual(font, r.GetFontAtIndex(i));
            }
            for (int i = 2; i < r.Length; i++)
            {
                Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
            }
        }
Ejemplo n.º 4
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet1=hssfworkbook.CreateSheet("Sheet1");

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

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

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

            //bind font with style 2
           ICellStyle style2 = hssfworkbook.CreateCellStyle();
            style2.SetFont(font2);
            
            //apply font styles
            ICell cell1 = HSSFCellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
            cell1.CellStyle = style1;
            ICell cell2 = HSSFCellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好!");
            cell2.CellStyle = style2;

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

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

            WriteToFile();
        }
Ejemplo n.º 5
0
        private static void DrawSheet4(ISheet sheet4, HSSFWorkbook wb)
        {
            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();

            // Create a couple of textboxes
            HSSFTextbox textbox1 = (HSSFTextbox)patriarch.CreateTextbox(
                new HSSFClientAnchor(0, 0, 0, 0, (short)1, 1, (short)2, 2));

            textbox1.String = new HSSFRichTextString("This is a test");
            HSSFTextbox textbox2 = (HSSFTextbox)patriarch.CreateTextbox(
                new HSSFClientAnchor(0, 0, 900, 100, (short)3, 3, (short)3, 4));

            textbox2.String = new HSSFRichTextString("Woo");
            textbox2.SetFillColor(200, 0, 0);
            textbox2.LineStyle = LineStyle.DotGel;

            // Create third one with some fancy font styling.
            HSSFTextbox textbox3 = (HSSFTextbox)patriarch.CreateTextbox(
                new HSSFClientAnchor(0, 0, 900, 100, (short)4, 4, (short)5, 4 + 1));
            IFont font = wb.CreateFont();

            font.IsItalic  = true;
            font.Underline = (byte)FontUnderlineType.DOUBLE;
            HSSFRichTextString str = new HSSFRichTextString("Woo!!!");

            str.ApplyFont(2, 5, font);
            textbox3.String    = str;
            textbox3.FillColor = 0x08000030;
            textbox3.LineStyle = LineStyle.None; // no line around the textbox.
            textbox3.IsNoFill  = true;           // make it transparent
        }
Ejemplo n.º 6
0
        public void SetText(CellPos pos, string text)
        {
            List <TextPosition> positions = new List <TextPosition>();

            while (text.IndexOf("<b>") > -1)
            {
                int start = text.IndexOf("<b>");

                text = text.Substring(0, start) + text.Substring(start + 3);
                int end = text.IndexOf("</b>");
                text = text.Substring(0, end) + text.Substring(end + 4);
                positions.Add(new TextPosition {
                    start = start, end = end
                });
            }
            HSSFRichTextString richtext = new HSSFRichTextString(text);
            IFont font1 = _parent.Internal.CreateFont();

            font1.Boldweight = (short)FontBoldWeight.Bold;
            foreach (TextPosition position in positions)
            {
                richtext.ApplyFont(position.start, position.end, font1);
            }

            ICell cell = GetCell(pos, true);

            cell.SetCellValue(richtext);
        }
Ejemplo n.º 7
0
        public void TestClearFormatting()
        {
            HSSFRichTextString r = new HSSFRichTextString("Testing");

            Assert.AreEqual(0, r.NumFormattingRuns);
            r.ApplyFont(2, 4, new HSSFFont((short)1, null));
            Assert.AreEqual(2, r.NumFormattingRuns);
            r.ClearFormatting();
            Assert.AreEqual(0, r.NumFormattingRuns);
        }
Ejemplo n.º 8
0
        public void TestApplyFont()
        {
            HSSFRichTextString r = new HSSFRichTextString("Testing");

            Assert.AreEqual(0, r.NumFormattingRuns);
            r.ApplyFont(2, 4, new HSSFFont((short)1, null));
            Assert.AreEqual(2, r.NumFormattingRuns);
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
            Assert.AreEqual(1, r.GetFontAtIndex(2));
            Assert.AreEqual(1, r.GetFontAtIndex(3));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(6));

            r.ApplyFont(6, 7, new HSSFFont((short)2, null));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
            Assert.AreEqual(1, r.GetFontAtIndex(2));
            Assert.AreEqual(1, r.GetFontAtIndex(3));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
            Assert.AreEqual(2, r.GetFontAtIndex(6));

            r.ApplyFont(HSSFRichTextString.NO_FONT);
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(2));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(3));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
            Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));

            r.ApplyFont(new HSSFFont((short)1, null));
            Assert.AreEqual(1, r.GetFontAtIndex(0));
            Assert.AreEqual(1, r.GetFontAtIndex(1));
            Assert.AreEqual(1, r.GetFontAtIndex(2));
            Assert.AreEqual(1, r.GetFontAtIndex(3));
            Assert.AreEqual(1, r.GetFontAtIndex(4));
            Assert.AreEqual(1, r.GetFontAtIndex(5));
            Assert.AreEqual(1, r.GetFontAtIndex(6));
        }
Ejemplo n.º 9
0
        public void TestStore()
        {
            TextObjectRecord   record = new TextObjectRecord();
            HSSFRichTextString str    = new HSSFRichTextString("AB");

            str.ApplyFont(0, 2, (short)0x0018);
            str.ApplyFont(2, 2, (short)0x0320);

            record.HorizontalTextAlignment = (TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_CENTERED);
            record.VerticalTextAlignment   = (TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_JUSTIFY);
            record.IsTextLocked            = (true);
            record.TextOrientation         = (TextObjectRecord.TEXT_ORIENTATION_ROT_RIGHT);
            record.Str = (str);

            byte[] recordBytes = record.Serialize();
            Assert.AreEqual(recordBytes.Length, data.Length);
            for (int i = 0; i < data.Length; i++)
            {
                Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
            }
        }
Ejemplo n.º 10
0
        private static void CreateOneRowTwoColumnCellsWithColumn0FontColor(ISheet sheet, ref int rowIndex, string column0_text, string column1_text, IFont font)
        {
            IRow            row        = sheet.CreateRow(rowIndex);
            ICell           cell       = row.CreateCell(0);
            IRichTextString richString = new HSSFRichTextString(column0_text);

            richString.ApplyFont(font);
            cell.SetCellValue(richString);
            cell = row.CreateCell(1);
            cell.SetCellValue(column1_text);
            rowIndex++;
        }
Ejemplo n.º 11
0
        public void TestStore()
        {
            TextObjectRecord   record = new TextObjectRecord();
            HSSFRichTextString str    = new HSSFRichTextString("AB");

            str.ApplyFont(0, 2, (short)0x0018);
            str.ApplyFont(2, 2, (short)0x0320);

            record.HorizontalTextAlignment = HorizontalAlignment.Center;
            record.VerticalTextAlignment   = VerticalAlignment.Justify;
            record.IsTextLocked            = (true);
            record.TextOrientation         = TextOrientation.RotRight;
            record.Str = (str);

            byte[] recordBytes = record.Serialize();
            Assert.AreEqual(recordBytes.Length, data.Length);
            for (int i = 0; i < data.Length; i++)
            {
                Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
            }
        }
Ejemplo n.º 12
0
        public void setTitle(DateTime date)
        {
            string             title     = string.Format("  南宁  分公司(办事处)  {0}  年  {1}  月   ", date.Year, date.Month);
            HSSFRichTextString Richtitle = new HSSFRichTextString(title);
            IFont font = hssfworkbook.CreateFont();

            font.Underline          = FontUnderlineType.Single;
            font.FontHeightInPoints = 16;
            font.FontName           = "黑体";
            font.IsBold             = true;
            IFont font1 = hssfworkbook.CreateFont();

            font1.FontHeightInPoints = 16;
            font1.FontName           = "黑体";
            font1.IsBold             = true;
            Richtitle.ApplyFont(0, 6, font);
            Richtitle.ApplyFont(6, 15, font1);
            Richtitle.ApplyFont(15, 22, font);
            Richtitle.ApplyFont(22, 24, font1);
            Richtitle.ApplyFont(24, 28, font);
            Richtitle.ApplyFont(28, 30, font1);
            ICell cell = hssfworkbook.GetSheet("Sheet1").GetRow(1).GetCell(1);

            cell.SetCellValue(Richtitle);
        }
Ejemplo n.º 13
0
        private void AnalysisCellString(HSSFCell cell)
        {
            string             st3  = string.Empty;
            HSSFRichTextString rich = (HSSFRichTextString)cell.RichStringCellValue;

            int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;

            if (formattingRuns == 0)
            {
                return;
            }
            IFont  font2 = _workBook.GetFontAt(cell.CellStyle.FontIndex);
            string st2   = rich.String.Substring(0, rich.GetIndexOfFormattingRun(0));

            if (font2.Color != IndexedColors.White.Index && font2.Color != 0)
            {
                st3 += st2;
            }
            for (int i = 0; i < formattingRuns; i++)
            {
                int startIdx = rich.GetIndexOfFormattingRun(i);
                int length;
                if (i == formattingRuns - 1)
                {
                    length = rich.Length - startIdx;
                }
                else
                {
                    length = rich.GetIndexOfFormattingRun(i + 1) - startIdx;
                }
                string st = rich.String.Substring(startIdx, length);

                short fontIndex = rich.GetFontOfFormattingRun(i);
                IFont font      = _workBook.GetFontAt(fontIndex);
                if (font.Color != IndexedColors.White.Index && font.Color != 0)
                {
                    font2 = font;
                    st3  += st;
                }
            }
            HSSFRichTextString rich2 = new HSSFRichTextString(st3);

            rich2.ApplyFont(font2);
            cell.SetCellValue(rich2);
        }
Ejemplo n.º 14
0
        public void TestOptimiseFonts()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            // Add 6 fonts, some duplicates
            IFont f1 = wb.CreateFont();

            f1.FontHeight = ((short)11);
            f1.FontName   = ("Testing");

            IFont f2 = wb.CreateFont();

            f2.FontHeight = ((short)22);
            f2.FontName   = ("Also Testing");

            IFont f3 = wb.CreateFont();

            f3.FontHeight = ((short)33);
            f3.FontName   = ("Unique");

            IFont f4 = wb.CreateFont();

            f4.FontHeight = ((short)11);
            f4.FontName   = ("Testing");

            IFont f5 = wb.CreateFont();

            f5.FontHeight = ((short)22);
            f5.FontName   = ("Also Testing");

            IFont f6 = wb.CreateFont();

            f6.FontHeight = ((short)66);
            f6.FontName   = ("Also Unique");



            // Use all three of the four in cell styles
            Assert.AreEqual(21, wb.NumCellStyles);

            NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
            cs1.SetFont(f1);
            Assert.AreEqual(5, cs1.FontIndex);

            NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
            cs2.SetFont(f4);
            Assert.AreEqual(8, cs2.FontIndex);

            NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
            cs3.SetFont(f5);
            Assert.AreEqual(9, cs3.FontIndex);

            NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
            cs4.SetFont(f6);
            Assert.AreEqual(10, cs4.FontIndex);

            Assert.AreEqual(25, wb.NumCellStyles);


            // And three in rich text
            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = s.CreateRow(0);

            HSSFRichTextString rtr1 = new HSSFRichTextString("Test");

            rtr1.ApplyFont(0, 2, f1);
            rtr1.ApplyFont(3, 4, f2);
            r.CreateCell(0).SetCellValue(rtr1);

            HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");

            rtr2.ApplyFont(0, 2, f3);
            rtr2.ApplyFont(3, 5, f5);
            rtr2.ApplyFont(6, 8, f6);
            r.CreateCell(1).SetCellValue(rtr2);


            // Check what we have now
            Assert.AreEqual(10, wb.NumberOfFonts);
            Assert.AreEqual(25, wb.NumCellStyles);

            // Optimise
            HSSFOptimiser.OptimiseFonts(wb);

            // Check font count
            Assert.AreEqual(8, wb.NumberOfFonts);
            Assert.AreEqual(25, wb.NumCellStyles);

            // Check font use in cell styles
            Assert.AreEqual(5, cs1.FontIndex);
            Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
            Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
            Assert.AreEqual(8, cs4.FontIndex); // two have gone


            // And in rich text

            // RTR 1 had f1 and f2, unchanged
            Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
            Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
            Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
            Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));

            // RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
            Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
            Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
            Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
            Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
            Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(6));
            Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(7));
        }
Ejemplo n.º 15
0
        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="datatable"></param>
        /// <param name="num"></param>
        private void CreateExcel(ISheet sheet, DataTable datatable, int num, string sheetname)
        {
            HSSFCellStyle cellStyle = (HSSFCellStyle)sheet.Workbook.CreateCellStyle();      //创建列头单元格实例样式

            cellStyle.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;     //水平居中
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;       //垂直居中
            cellStyle.WrapText          = true;

            IRow firstrow;

            if (num == 0)
            {
                firstrow = sheet.CreateRow(0);
            }
            else
            {
                firstrow = sheet.GetRow(0);
            }
            ICell dFirstCell = firstrow.CreateCell(2);

            dFirstCell.SetCellValue("K");
            dFirstCell.CellStyle = cellStyle;
            ICell firstCell = firstrow.CreateCell(num + 3);

            firstCell.SetCellValue(datatable.TableName);
            firstCell.CellStyle = cellStyle;
            for (int i = 0; i < datatable.Rows.Count; i++)
            {
                IRow   row;
                string sampleName = datatable.Rows[i][0].ToString();
                string d          = datatable.Rows[i][1].ToString();
                //要加一行稀释倍数K
                if (num == 0)
                {
                    row = sheet.CreateRow(i + 1);
                    ICell numCell = row.CreateCell(0);
                    numCell.SetCellValue(i + 1);
                    ICell nameCell = row.CreateCell(1);
                    nameCell.SetCellValue(sampleName);
                    ICell dCell = row.CreateCell(2);
                    dCell.SetCellValue(d);
                    numCell.CellStyle  = cellStyle;
                    nameCell.CellStyle = cellStyle;
                    dCell.CellStyle    = cellStyle;
                }
                else
                {
                    row = sheet.GetRow(i + 1);
                }
                ICell cell = row.CreateCell(num + 3);
                cell.CellStyle = cellStyle;
                if (sheetname == datatable.Columns[2].ColumnName)
                {
                    string data = datatable.Rows[i][2].ToString();
                    if (data != "/")
                    {
                        data = CalculateAccuracyCX(data, 3);
                    }
                    cell.SetCellValue(data);
                }
                else
                {
                    string displayResult = string.Empty;
                    try
                    {
                        if (sampleName.Contains("平均"))
                        {
                            int     rowNum = row.RowNum;
                            HSSFRow row1   = sheet.GetRow(rowNum - 1) as HSSFRow;
                            HSSFRow row2   = sheet.GetRow(rowNum - 2) as HSSFRow;
                            string  data1  = row1.GetCell(num + 3).StringCellValue;
                            string  data2  = row2.GetCell(num + 3).StringCellValue;
                            if (!data1.Contains("/") && !data2.Contains("/") && !data1.Contains("<") && !data2.Contains("<") && !data1.Contains("×") && !data2.Contains("×"))
                            {
                                decimal startData = (decimal.Parse(data1) + decimal.Parse(data2)) / 2;
                                //开始修约计算
                                displayResult        = RoundCalculationResult(datatable.TableName, startData);
                                datatable.Rows[i][3] = displayResult;
                            }
                            else if (data1.Contains("<"))
                            {
                                datatable.Rows[i][3] = data1;
                                displayResult        = data1;
                            }
                            else if (data2.Contains("<"))
                            {
                                datatable.Rows[i][3] = data2;
                                displayResult        = data2;
                            }
                            else if (data1.Contains("/") || data2.Contains("/"))
                            {
                                datatable.Rows[i][3] = "/";
                                displayResult        = "/";
                            }
                            else if (data1.Contains("×") || data2.Contains("×"))
                            {
                                decimal CC;
                                decimal CCC;
                                if (data1.Contains("×"))
                                {
                                    string[] str = data1.Split("×");
                                    CC = decimal.Parse(str[0]);
                                    for (int j = 0; j < int.Parse(str[1].Replace("10", "")); j++)
                                    {
                                        CC *= 10;
                                    }
                                }
                                else
                                {
                                    CC = decimal.Parse(data1);
                                }
                                if (data2.Contains("×"))
                                {
                                    string[] str = data2.Split("×");
                                    CCC = decimal.Parse(str[0]);
                                    for (int j = 0; j < int.Parse(str[1].Replace("10", "")); j++)
                                    {
                                        CCC *= 10;
                                    }
                                }
                                else
                                {
                                    CCC = decimal.Parse(data2);
                                }
                                //平均要把科学计数法变回正常数
                                decimal C = (CC + CCC) / 2;
                                displayResult        = RoundCalculationResult(datatable.TableName, C);
                                datatable.Rows[i][3] = displayResult;
                            }
                            else
                            {
                                HSSFCellStyle cellRedStyle = CreateRedStyle((HSSFWorkbook)sheet.Workbook);
                                cell.CellStyle       = cellRedStyle;
                                datatable.Rows[i][3] = "/";
                                displayResult        = "/";
                            }
                        }
                        else if (datatable.Rows[i][2].ToString().Contains("/"))
                        {
                            datatable.Rows[i][3] = "/";
                            displayResult        = "/";
                        }
                        else
                        {
                            decimal startData = decimal.Parse(datatable.Rows[i][2].ToString());
                            decimal voidData  = decimal.Zero;
                            foreach (KeyValuePair <string, decimal> item in voidElementList)
                            {
                                if (item.Key == datatable.TableName)
                                {
                                    voidData = item.Value;
                                }
                            }
                            decimal dilution = decimal.Parse(datatable.Rows[i][1].ToString());
                            decimal answer   = dilution * (startData - voidData);
                            //开始修约计算
                            displayResult        = RoundCalculationResult(datatable.TableName, answer);
                            datatable.Rows[i][3] = displayResult;
                        }
                    }
                    catch
                    {
                        string error = "在计算元素[" + datatable.TableName + "]的时候,[" + sampleName + "]的原始数据出现了错误,请联系信息化人员处理。";
                        MessageBox.Show(error);
                    }

                    if (displayResult.Contains("×"))
                    {
                        HSSFRichTextString rts = new HSSFRichTextString(displayResult);
                        var cellStyleFont      = (HSSFFont)sheet.Workbook.CreateFont();          //创建字体
                        cellStyleFont.TypeOffset = FontSuperScript.Super;                        //字体上标下标
                        rts.ApplyFont(displayResult.Length - 1, displayResult.Length, cellStyleFont);
                        cell.SetCellValue(rts);
                    }
                    else
                    {
                        cell.SetCellValue(displayResult);
                    }
                }
            }

            //自动调整列距
            for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
            {
                sheet.AutoSizeColumn(i);
                if (i == 1)
                {
                    sheet.SetColumnWidth(i, 30 * 256);
                }
                int width = sheet.GetColumnWidth(i);
                if (width < 10 * 256)
                {
                    sheet.SetColumnWidth(i, 10 * 256);
                }
            }
        }
Ejemplo n.º 16
0
        public IActionResult DownExcel(string data)
        {
            List <JObject> dowExcels = JsonConvert.DeserializeObject <List <JObject> >(data);
            IWorkbook      workbook;

            workbook = new HSSFWorkbook();
            //赋予表格默认样式,因为样式文件存储只能有4000个
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.VerticalAlignment   = VerticalAlignment.Center;   //垂直居中
            cellStyle.WrapText            = true;                       //自动换行
            cellStyle.Alignment           = HorizontalAlignment.Center; //水平居中
            cellStyle.FillPattern         = FillPattern.NoFill;         //背景色是必须要这数据
            cellStyle.FillForegroundColor = IndexedColors.White.Index;  //默认背景色
            foreach (var item in dowExcels)
            {
                var columnlen  = item["config"] != null ? item["config"]["columnlen"] : null;
                var rowlen     = item["config"] != null ? item["config"]["rowlen"] : null;
                var borderInfo = item["config"] != null ? item["config"]["borderInfo"] : null;
                var merge      = item["config"] != null ? item["config"]["merge"] : null;
                var Cjarray    = item["celldata"];

                //读取了模板内所有sheet内容
                ISheet sheet = workbook.CreateSheet(item["name"].ToString());
                //判断是否有值,并且赋予样式
                if (Cjarray != null)
                {
                    for (int i = 0; i < Cjarray.Count(); i++)
                    {
                        //判断行,存不存在,不存在创建
                        IRow row = sheet.GetRow(int.Parse(Cjarray[i]["r"].ToString()));
                        if (row == null)
                        {
                            row = sheet.CreateRow(int.Parse(Cjarray[i]["r"].ToString()));
                        }
                        var ct  = Cjarray[i]["v"]["ct"];
                        var cct = Cjarray[i]["v"];
                        if (ct != null && ct["s"] != null)
                        {
                            //合并单元格的走这边
                            string celldatas = "";
                            //合并单元格时,会导致文字丢失,提前处理文字信息
                            for (int j = 0; j < ct["s"].Count(); j++)
                            {
                                var stv = ct["s"][j];
                                celldatas += stv["v"] != null ? stv["v"].ToString() : "";
                            }
                            //判断列,不存在创建
                            ICell Cell = row.GetCell(int.Parse(Cjarray[i]["c"].ToString()));
                            if (Cell == null)
                            {
                                HSSFRichTextString richtext = new HSSFRichTextString(celldatas);
                                Cell = row.CreateCell(int.Parse(Cjarray[i]["c"].ToString()));
                                for (int k = 0; k < ct["s"].Count(); k++)
                                {
                                    IFont font = workbook.CreateFont();
                                    var   stv  = ct["s"][k];
                                    //文字颜色
                                    if (stv["fc"] != null)
                                    {
                                        var rGB   = HTMLColorTranslator.Translate(stv["fc"].ToString());
                                        var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        font.Color = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
                                    }
                                    else
                                    {
                                        font.Color = HSSFColor.Black.Index;
                                    }
                                    //是否加粗
                                    if (stv["bl"] != null)
                                    {
                                        font.IsBold     = !string.IsNullOrEmpty(stv["bl"].ToString()) && (stv["bl"].ToString() == "1" ? true : false);
                                        font.Boldweight = stv["bl"].ToString() == "1" ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
                                    }
                                    else
                                    {
                                        font.IsBold     = false;
                                        font.Boldweight = (short)FontBoldWeight.None;
                                    }
                                    //是否斜体
                                    if (stv["it"] != null)
                                    {
                                        font.IsItalic = !string.IsNullOrEmpty(stv["it"].ToString()) && (stv["it"].ToString() == "1" ? true : false);
                                    }
                                    else
                                    {
                                        font.IsItalic = false;
                                    }
                                    //下划线
                                    if (stv["un"] != null)
                                    {
                                        font.Underline = stv["un"].ToString() == "1" ? FontUnderlineType.Single : FontUnderlineType.None;
                                    }
                                    else
                                    {
                                        font.Underline = FontUnderlineType.None;
                                    }
                                    //字体
                                    if (stv["ff"] != null)
                                    {
                                        font.FontName = stv["ff"].ToString();
                                    }
                                    //文字大小
                                    if (stv["fs"] != null)
                                    {
                                        font.FontHeightInPoints = double.Parse(stv["fs"].ToString());
                                    }
                                    Cell.CellStyle.SetFont(font);
                                    richtext.ApplyFont(celldatas.IndexOf(stv["v"].ToString()), celldatas.IndexOf(stv["v"].ToString()) + stv["v"].ToString().Length, font);
                                    Cell.SetCellValue(richtext);
                                }
                                //背景颜色
                                if (cct["bg"] != null)
                                {
                                    ICellStyle cellStyle1 = workbook.CreateCellStyle();
                                    cellStyle1.CloneStyleFrom(cellStyle);
                                    if (cct["bg"] != null)
                                    {
                                        var rGB   = HTMLColorTranslator.Translate(cct["bg"].ToString());
                                        var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        cellStyle1.FillPattern         = FillPattern.SolidForeground;
                                        cellStyle1.FillForegroundColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
                                    }
                                    Cell.CellStyle = cellStyle1;
                                }
                                else
                                {
                                    Cell.CellStyle = cellStyle;
                                }
                            }
                        }
                        else
                        {
                            //没有合并单元格的走这边
                            //判断列,不存在创建
                            ICell Cell = row.GetCell(int.Parse(Cjarray[i]["c"].ToString()));
                            if (Cell == null)
                            {
                                Cell = row.CreateCell(int.Parse(Cjarray[i]["c"].ToString()));
                                IFont font = workbook.CreateFont();
                                ct = Cjarray[i]["v"];
                                //字体颜色
                                if (ct["fc"] != null)
                                {
                                    var rGB   = HTMLColorTranslator.Translate(ct["fc"].ToString());
                                    var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                    font.Color = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
                                }
                                else
                                {
                                    font.Color = HSSFColor.Black.Index;
                                }
                                //是否加粗
                                if (ct["bl"] != null)
                                {
                                    font.IsBold     = !string.IsNullOrEmpty(ct["bl"].ToString()) && (ct["bl"].ToString() == "1" ? true : false);
                                    font.Boldweight = ct["bl"].ToString() == "1" ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
                                }
                                else
                                {
                                    font.IsBold     = false;
                                    font.Boldweight = (short)FontBoldWeight.None;
                                }
                                //斜体
                                if (ct["it"] != null)
                                {
                                    font.IsItalic = !string.IsNullOrEmpty(ct["it"].ToString()) && (ct["it"].ToString() == "1" ? true : false);
                                }
                                else
                                {
                                    font.IsItalic = false;
                                }
                                //下划线
                                if (ct["un"] != null)
                                {
                                    font.Underline = ct["un"].ToString() == "1" ? FontUnderlineType.Single : FontUnderlineType.None;
                                }
                                else
                                {
                                    font.Underline = FontUnderlineType.None;
                                }
                                //字体
                                if (ct["ff"] != null)
                                {
                                    font.FontName = ct["ff"].ToString();
                                }
                                //文字大小
                                if (ct["fs"] != null)
                                {
                                    font.FontHeightInPoints = double.Parse(ct["fs"].ToString());
                                }
                                Cell.CellStyle.SetFont(font);
                                //判断背景色
                                if (ct["bg"] != null)
                                {
                                    ICellStyle cellStyle1 = workbook.CreateCellStyle();
                                    cellStyle1.CloneStyleFrom(cellStyle);
                                    if (ct["bg"] != null)
                                    {
                                        var rGB   = HTMLColorTranslator.Translate(ct["bg"].ToString());
                                        var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        cellStyle1.FillPattern         = FillPattern.SolidForeground;
                                        cellStyle1.FillForegroundColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
                                    }
                                    Cell.CellStyle = cellStyle1;
                                }
                                else
                                {
                                    Cell.CellStyle = cellStyle;
                                }
                                Cell.SetCellValue(ct["v"] != null ? ct["v"].ToString() : "");
                            }
                        }
                    }
                    sheet.ForceFormulaRecalculation = true;
                }
                //判断是否要设置列宽度
                if (columnlen != null)
                {
                    foreach (var cols in columnlen)
                    {
                        var p = cols as JProperty;
                        sheet.SetColumnWidth(int.Parse(p.Name), int.Parse(p.Value.ToString()) * 38);
                    }
                }
                //判断是否要设置行高度
                if (rowlen != null)
                {
                    foreach (var rows in rowlen)
                    {
                        var p = rows as JProperty;
                        sheet.GetRow(int.Parse(p.Name)).HeightInPoints = float.Parse(p.Value.ToString());
                    }
                }
                //判断是否要加边框
                if (borderInfo != null)
                {
                    for (int i = 0; i < borderInfo.Count(); i++)
                    {
                        var bordervalue = borderInfo[i]["value"];
                        if (bordervalue != null)
                        {
                            var rowindex = bordervalue["row_index"];
                            var colindex = bordervalue["col_index"];
                            var l        = bordervalue["l"];
                            var r        = bordervalue["r"];
                            var t        = bordervalue["t"];
                            var b        = bordervalue["b"];
                            if (rowindex != null)
                            {
                                IRow rows = sheet.GetRow(int.Parse(bordervalue["row_index"].ToString()));
                                if (colindex != null)
                                {
                                    ICell cell = rows.GetCell(int.Parse(bordervalue["col_index"].ToString()));
                                    if (b != null)
                                    {
                                        cell.CellStyle.BorderBottom = ExcelHepler.GetBorderStyle(int.Parse(b["style"].ToString()));
                                        var rGB    = HTMLColorTranslator.Translate(b["color"].ToString());
                                        var bcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        cell.CellStyle.BottomBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(bcolor.R, bcolor.G, bcolor.B).Indexed;
                                    }
                                    else
                                    {
                                        cell.CellStyle.BorderBottom      = BorderStyle.None;
                                        cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
                                    }
                                    if (t != null)
                                    {
                                        cell.CellStyle.BorderTop = ExcelHepler.GetBorderStyle(int.Parse(t["style"].ToString()));
                                        var rGB    = HTMLColorTranslator.Translate(t["color"].ToString());
                                        var tcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        cell.CellStyle.TopBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(tcolor.R, tcolor.G, tcolor.B).Indexed;
                                    }
                                    else
                                    {
                                        cell.CellStyle.BorderBottom      = BorderStyle.None;
                                        cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
                                    }
                                    if (l != null)
                                    {
                                        cell.CellStyle.BorderLeft = ExcelHepler.GetBorderStyle(int.Parse(l["style"].ToString()));
                                        var rGB    = HTMLColorTranslator.Translate(l["color"].ToString());
                                        var lcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        cell.CellStyle.LeftBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(lcolor.R, lcolor.G, lcolor.B).Indexed;
                                    }
                                    else
                                    {
                                        cell.CellStyle.BorderBottom      = BorderStyle.None;
                                        cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
                                    }
                                    if (r != null)
                                    {
                                        cell.CellStyle.BorderRight = ExcelHepler.GetBorderStyle(int.Parse(r["style"].ToString()));
                                        var rGB    = HTMLColorTranslator.Translate(r["color"].ToString());
                                        var rcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
                                        cell.CellStyle.RightBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(rcolor.R, rcolor.G, rcolor.B).Indexed;
                                    }
                                    else
                                    {
                                        cell.CellStyle.BorderBottom      = BorderStyle.None;
                                        cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
                                    }
                                }
                            }
                        }
                    }
                }
                //判断是否要合并单元格
                if (merge != null)
                {
                    foreach (var imerge in merge)
                    {
                        var firstmer            = imerge.First();
                        int r                   = int.Parse(firstmer["r"].ToString());  //主单元格的行号,开始行号
                        int rs                  = int.Parse(firstmer["rs"].ToString()); //合并单元格占的行数,合并多少行
                        int c                   = int.Parse(firstmer["c"].ToString());  //主单元格的列号,开始列号
                        int cs                  = int.Parse(firstmer["cs"].ToString()); //合并单元格占的列数,合并多少列
                        CellRangeAddress region = new CellRangeAddress(r, r + rs - 1, c, c + cs - 1);
                        sheet.AddMergedRegion(region);
                    }
                }
            }

            var dir = Path.Combine(Directory.GetCurrentDirectory(), @"wwwroot\Files");

            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            var        fileId   = Guid.NewGuid();
            var        fileName = $"{fileId}.xls";
            FileStream stream   = new FileStream(Path.Combine(dir, fileName), FileMode.OpenOrCreate);

            workbook.Write(stream);
            stream.Seek(0, SeekOrigin.Begin);
            workbook.Close();
            stream.Close();
            return(Ok(fileId));
        }
Ejemplo n.º 17
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet = hssfworkbook.CreateSheet("ICell comments in POI HSSF");

            // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
            IDrawing patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

            //Create a cell in row 3
            ICell cell1 = sheet.CreateRow(3).CreateCell(1);

            cell1.SetCellValue(new HSSFRichTextString("Hello, World"));

            //anchor defines size and position of the comment in worksheet
            IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));

            // set text in the comment
            comment1.String = (new HSSFRichTextString("We can set comments in POI"));

            //set comment author.
            //you can see it in the status bar when moving mouse over the commented cell
            comment1.Author = ("Apache Software Foundation");

            // The first way to assign comment to a cell is via HSSFCell.SetCellComment method
            cell1.CellComment = (comment1);

            //Create another cell in row 6
            ICell cell2 = sheet.CreateRow(6).CreateCell(1);

            cell2.SetCellValue(36.6);


            HSSFComment comment2 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));

            //modify background color of the comment
            comment2.SetFillColor(204, 236, 255);

            HSSFRichTextString str = new HSSFRichTextString("Normal body temperature");

            //apply custom font to the text in the comment
            IFont font = hssfworkbook.CreateFont();

            font.FontName           = ("Arial");
            font.FontHeightInPoints = 10;
            font.Boldweight         = (short)FontBoldWeight.Bold;
            font.Color = HSSFColor.Red.Index;
            str.ApplyFont(font);

            comment2.String  = str;
            comment2.Visible = true; //by default comments are hidden. This one is always visible.

            comment2.Author = "Bill Gates";

            /**
             * The second way to assign comment to a cell is to implicitly specify its row and column.
             * Note, it is possible to set row and column of a non-existing cell.
             * It works, the commnet is visible.
             */
            comment2.Row    = 6;
            comment2.Column = 1;

            WriteToFile();
        }
        /// <summary>
        /// 构建一个Sheet
        /// </summary>
        /// <param name="sheet"></param>
        public void CreteSheet(NPOIExcelSheet sheet)
        {
            ISheet curSheet = _book.CreateSheet(sheet.SheetName);

            //表格
            int rowIndex = 0;

            if (sheet.Rows != null && sheet.Rows.Count > 0)
            {
                foreach (var row in sheet.Rows)
                {
                    IRow curRow = curSheet.CreateRow(rowIndex);

                    int cellIndex = 0;

                    foreach (var cell in row.Cells)
                    {
                        ICell curCell = curRow.CreateCell(cellIndex, cell.Type);
                        cell.Value = cell.Value ?? "";

                        if (cell.Style != null)
                        {
                            curCell.CellStyle = cell.Style;
                        }
                        //富文本设置
                        if (cell.RichTextSettings != null && cell.RichTextSettings.Any())
                        {
                            if (_excelType == NPOIExcelType.XLSX)
                            {
                                //SXSSFWorkbook

                                XSSFRichTextString xSSF = new XSSFRichTextString(cell.Value);
                                cell.RichTextSettings.ForEach(setting => { xSSF.ApplyFont(setting.Start, setting.End, setting.Font); });
                                curCell.SetCellValue(xSSF);
                            }
                            else
                            {
                                HSSFRichTextString hSSF = new HSSFRichTextString(cell.Value);
                                cell.RichTextSettings.ForEach(setting => { hSSF.ApplyFont(setting.Start, setting.End, setting.Font); });
                                curCell.SetCellValue(hSSF);
                            }
                        }
                        else
                        {
                            curCell.SetCellValue(cell.Value);
                        }

                        //自动宽度
                        if (cell.Width >= 0)
                        {
                            if (cell.Width == 0)
                            {
                                AutoWidth(curSheet, cellIndex);
                            }
                            else
                            {
                                curSheet.SetColumnWidth(cellIndex, (int)cell.Width * 256);
                            }
                        }


                        cellIndex++;
                    }
                    curRow.Height = (short)Convert.ToInt32(row.Height * 20);

                    //合并单元格策略
                    if (row.Regions.Any())
                    {
                        int maxRow = 0;
                        foreach (var region in row.Regions)
                        {
                            //if (maxRow < rowIndex + region.RowCount - 1)
                            //{
                            //    maxRow = rowIndex + region.RowCount - 1;
                            //}
                            curSheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + region.RowCount - 1, region.StartCol, region.EndCol));
                        }

                        // rowIndex += maxRow > 0 ? maxRow : 1;
                    }
                    rowIndex++;

                    rowIndex += row.MaginButton;
                }
            }

            //图表
            if (sheet.Chart != null && sheet.Chart.Count > 0)
            {
                int startRow = rowIndex + 1;
                foreach (var curChart in sheet.Chart)
                {
                    int endRow = startRow + curChart.MarginBottom;
                    CreateChart(curChart, curSheet, startRow, endRow);
                    startRow = endRow + 1;
                }
            }
        }
Ejemplo n.º 19
0
        public void DrawString(String str, int x, int y)
        {
            if (string.IsNullOrEmpty(str))
                return;

            using (Font excelFont = new Font(font.Name.Equals("SansSerif") ? "Arial" : font.Name, (int)(font.Size / verticalPixelsPerPoint), font.Style))
            {
                FontDetails d = StaticFontMetrics.GetFontDetails(excelFont);
                int width = (int)((d.GetStringWidth(str) * 8) + 12);
                int height = (int)((font.Size / verticalPixelsPerPoint) + 6) * 2;
                y -= Convert.ToInt32((font.Size / verticalPixelsPerPoint) + 2 * verticalPixelsPerPoint);    // we want to Draw the shape from the top-left
                HSSFTextbox textbox = escherGroup.CreateTextbox(new HSSFChildAnchor(x, y, x + width, y + height));
                textbox.IsNoFill = (true);
                textbox.LineStyle = LineStyle.None;
                HSSFRichTextString s = new HSSFRichTextString(str);
                HSSFFont hssfFont = MatchFont(excelFont);
                s.ApplyFont(hssfFont);
                textbox.String = (s);
            }
        }