private void footerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("foot"); ExcelBorder border = getBorder(); if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont(FontFamily, FooterFontSize); font.Bold = true; if (HeaderTextColor != "FF000000") { font.Color = HeaderTextColor; } for (uint row = 1; row <= cols.Length; row++) { uint rowInd = (uint)(row + headerOffset); sheet.Rows[rowInd].Height = RowHeight; for (uint col = 1; col <= cols[row - 1].Length; col++) { if (BGColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = BGColor; } sheet.Cells[rowInd, col].Style.Font = font; //TODO add text color, vertical alignment, horizontal alignment sheet.Cells[rowInd, col].Style.Border = border; sheet.Cells[rowInd, col].Value = cols[row - 1][col - 1].GetName(); } } } headerOffset += cols.Length; }
public void setColor(long value) { try { if (readOnly) { m_errAccess.setErrDes("Can not modify a readonly document"); m_errAccess.setErrCod((short)13); return; } for (int i = 1; i <= cntCells; i++) { ExcelFont fontCell = GetFont(i); int val = (int)value; fontCell.Color.SetColor(GXExcelHelper.ResolveColor(val)); } } catch (Exception e) { GXLogging.Error(log, "setcolor error", e); m_errAccess.setErrDes("Invalid font properties"); m_errAccess.setErrCod((short)6); } }
internal static ExtendedFormat GetExcelStyle(XFRecrod xf, ExcelFont font) { ExcelBorder border2 = new ExcelBorder(); ExcelBorderSide side = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.LeftBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.LeftBorderLine }; border2.Left = side; ExcelBorderSide side2 = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.RightBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.RightBorderLine }; border2.Right = side2; ExcelBorderSide side3 = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.TopBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.TopBorderLine }; border2.Top = side3; ExcelBorderSide side4 = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.BottomBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.BottomBorderLine }; border2.Bottom = side4; ExcelBorder border = border2; ExtendedFormat format = new ExtendedFormat { Font = font, Border = border, PatternBackgroundColor = new ExcelColor(ExcelColorType.Indexed, xf.FillPatternBackgroundColor, 0.0), PatternColor = new ExcelColor(ExcelColorType.Indexed, xf.FillPatternColor, 0.0), FillPattern = (FillPatternType)xf.FillPatternIndex, HorizontalAlign = (ExcelHorizontalAlignment)xf.HAlignment, VerticalAlign = (ExcelVerticalAlignment)xf.VAlignment, Indent = xf.IndentLevel, IsHidden = xf.IsHidden, ApplyAlignment = xf.ApplyAlignment, ApplyBorder = xf.ApplyBorder, ApplyFont = xf.ApplyFont, ApplyFill = xf.ApplyFill, ApplyNumberFormat = xf.ApplyNumberFormat, ApplyProtection = xf.ApplyProtection, IsStyleFormat = xf.IsStyleXF, IsJustfyLastLine = xf.JustifyLastCharacter, IsLocked = xf.IsLocked, IsShrinkToFit = xf.IsShrinkContent, IsWordWrap = xf.IsWordWrap, ReadingOrder = (TextDirection)xf.Direction, Rotation = xf.TextRotation, IsFirstSymbolApostrophe = xf.IsF123Prefix }; if (!format.IsStyleFormat) { format.ParentFormatID = new int?(xf.ParentXFIndex); } return(format); }
private void btnRun_Click(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; ExcelFont fontBold = workbook.CreateFont(); fontBold.IsBold = true; ExcelFont fontUnderline = workbook.CreateFont(); fontUnderline.Underline = FontUnderlineType.Single; ExcelFont fontColor = workbook.CreateFont(); fontColor.KnownColor = ExcelColors.Green; RichText richText = sheet.Range["A1"].RichText; richText.Text = "Bold and underlined and colored text"; richText.SetFont(0, 3, fontBold); richText.SetFont(9, 18, fontUnderline); richText.SetFont(24, 30, fontColor); workbook.SaveToFile("Sample.xls"); ExcelDocViewer(workbook.FileName); }
internal static Font GetGeoFont(TShapeProperties ShProp) { if (ShProp.ShapeOptions == null) { return(new Font("Arial", 10)); } real Size = ShProp.ShapeOptions.As1616(TShapeOption.gtextSize, 36); string FontName = ShProp.ShapeOptions.AsUnicodeString(TShapeOption.gtextFont, "Arial"); FontStyle Style = FontStyle.Regular; if (ShProp.ShapeOptions.AsBool(TShapeOption.gtextFStrikethrough, false, 5)) { Style |= FontStyle.Bold; } if (ShProp.ShapeOptions.AsBool(TShapeOption.gtextFStrikethrough, false, 4)) { Style |= FontStyle.Italic; } /* UnderLine and StrikeOut are not supported by Excel <= 2003. Even if the file has those bits set (Excel 2007 saves them) * * if (ShProp.ShapeOptions.AsBool(TShapeOption.gtextFStrikethrough, false, 3)) Style |= FontStyle.Underline; * if (ShProp.ShapeOptions.AsBool(TShapeOption.gtextFStrikethrough, false, 0)) Style |= FontStyle.Strikeout; */ return(ExcelFont.CreateFont(FontName, Size, Style)); }
private void btnRun_Click(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.Range["A1"].Value = "1"; sheet.Range["A2"].Value = "2"; sheet.Range["A3"].Value = "3"; sheet.Range["B1"].Value = "4"; sheet.Range["B2"].Value = "5"; sheet.Range["B3"].Value = "6"; Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered); chart.DataRange = sheet.Range["A1:B3"]; chart.TopRow = 4; chart.LeftColumn = 2; chart.Width = 450; chart.Height = 300; ExcelFont font = workbook.CreateFont(); font.Size = 20.0; font.Color = Color.Red; foreach (ChartSerie cs in chart.Series) { cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; cs.DataPoints.DefaultDataPoint.DataLabels.TextArea.SetFont(font); } workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007); ExcelDocViewer(workbook.FileName); }
private void btnRun_Click(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); //Initailize worksheet workbook.CreateEmptySheets(1); Worksheet sheet = workbook.Worksheets[0]; sheet.SetColumnWidth(1, 25); //Creates font ExcelFont fontBlue = workbook.CreateFont(); fontBlue.KnownColor = ExcelColors.LightBlue; ExcelFont fontGreen = workbook.CreateFont(); fontGreen.KnownColor = ExcelColors.Red; CellRange range = sheet.Range["A1"]; range.Text = "Regular comment"; range.Comment.Text = "Regular comment"; //Regular comment range = sheet.Range["A2"]; range.Text = "Rich text comment"; //Rich text comment range.Comment.RichText.Text = "Rich text comment"; range.Comment.RichText.SetFont(0, 4, fontGreen); range.Comment.RichText.SetFont(5, 9, fontBlue); workbook.SaveToFile("Sample.xls"); ExcelDocViewer(workbook.FileName); }
/// <summary> /// 设置数据单元格的字体 /// </summary> public virtual void SetDataCellFont(ExcelFont font, DataFontAttribute fontAttr) { if (font == null) { return; } fontAttr = fontAttr ?? new DataFontAttribute(); // font.FontHeight = fontAttr.FontHeight; // font.Charset = fontAttr.Charset; // font.TypeOffset = (FontSuperScript)fontAttr.TypeOffset; if (!string.IsNullOrWhiteSpace(fontAttr.FontName)) { font.Name = fontAttr.FontName; } font.Italic = fontAttr.IsItalic; font.Strike = fontAttr.IsStrikeout; font.Bold = fontAttr.IsBold; if (fontAttr.Underline > -1) { font.UnderLineType = (ExcelUnderLineType)fontAttr.Underline; } if (fontAttr.FontHeightInPoints > -1) { font.Size = fontAttr.FontHeightInPoints; } if (fontAttr.Color > -1) { font.Color.Indexed = fontAttr.Color; } }
public void BuildReport(Window owner) { if (string.IsNullOrEmpty(PrinterName) && string.IsNullOrWhiteSpace(PrinterName)) { MessageBox.Show(owner, "Please select your printer name.", "Atention", MessageBoxButton.OK, MessageBoxImage.Information); return; } try { string dataDir = System.IO.Directory.GetCurrentDirectory(); string fullFileName = dataDir + @"\templates\"; Workbook workbook = new Workbook(); workbook.LoadFromFile(fullFileName + "Template.xlsx"); //Edit Text Worksheet sheet = workbook.Worksheets[0]; DataTable data = GetData(); ExcelFont fontBold = workbook.CreateFont(); sheet.PageSetup.Orientation = PageOrientationType.Landscape; fontBold.IsBold = true; var headers = data.AsEnumerable() .Select(s => s.Field <string>("Name")) .ToArray(); var cellsData = data.AsEnumerable() .Select(s => s.Field <int>("Type")) .ToArray(); int columnsRange = 3 + headers.Length; sheet.InsertArray(headers, 16, 3, false); sheet.InsertArray(cellsData, 17, 3, false); CellRange range = sheet.Range[16, 3, 16, columnsRange]; range.Style.Rotation = 60; range.Style.Font.IsBold = true; range.AutoFitRows(); range.ColumnWidth = 4; sheet.Range[16, 3, 17, columnsRange].BorderInside(LineStyleType.Thin, Color.Black); sheet.Range[16, 3, 17, columnsRange].BorderAround(LineStyleType.Thin, Color.Black); workbook.SaveToFile(fullFileName + "report.xlsx", FileFormat.Version2016); MessageBox.Show(owner, "Report has been created successfully.", "Information", MessageBoxButton.OK, MessageBoxImage.Information); PrintFile(owner, fullFileName + "report.xlsx"); } catch (Exception ex) { MessageBox.Show(owner, string.Format("An error has occurred while creating document: {0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error); } }
public ExcelFontInstance(ObjectInstance prototype, ExcelFont excelFont) : this(prototype) { if (excelFont == null) { throw new ArgumentNullException("excelFont"); } m_excelFont = excelFont; }
public void FormattingCells() { using (var package = new ExcelPackage()) { var sheet = package.Workbook.Worksheets.Add("Styling"); // Cells with style ExcelFont font = sheet.Cells["A1"].Style.Font; sheet.Cells["A1"].Value = "Bold and proud"; sheet.Cells["A1"].Style.Font.Name = "Arial"; font.Bold = true; font.Color.SetColor(Color.Green); // ExcelFont also has: Size, Italic, Underline, Strike, ... sheet.Cells["A3"].Style.Font.SetFromFont(new Font(new FontFamily("Arial"), 15, FontStyle.Strikeout)); sheet.Cells["A3"].Value = "SetFromFont(Font)"; // Borders need to be made sheet.Cells["A1:A2"].Style.Border.BorderAround(ExcelBorderStyle.Dotted); sheet.Cells[5, 5, 9, 8].Style.Border.BorderAround(ExcelBorderStyle.Dotted); // Merge cells sheet.Cells[5, 5, 9, 8].Merge = true; // More style sheet.Cells["D14"].Style.ShrinkToFit = true; sheet.Cells["D14"].Style.Font.Size = 24; sheet.Cells["D14"].Value = "Shrinking for fit"; sheet.Cells["D15"].Style.WrapText = true; sheet.Cells["D15"].Value = "A wrap, yummy!"; sheet.Cells["D16"].Value = "No wrap, ouch!"; // Setting a background color requires setting the PatternType first sheet.Cells["F6:G8"].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells["F6:G8"].Style.Fill.BackgroundColor.SetColor(Color.Red); // Horizontal Alignment needs a little workaround // http://stackoverflow.com/questions/34660560/epplus-isnt-honoring-excelhorizontalalignment-center-or-right var centerStyle = package.Workbook.Styles.CreateNamedStyle("Center"); centerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells["B5"].StyleName = "Center"; sheet.Cells["B5"].Value = "I'm centered"; // MIGHT NOT WORK: sheet.Cells["B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells["B6"].Value = "I'm not centered? :("; // Check for an example of Conditional formatting: // https://github.com/JanKallman/EPPlus/wiki/Conditional-formatting package.SaveAs(new FileInfo(BinDir.GetPath())); } }
public static void SetStyle(this ExcelFont Font, styleTextFontSingle styleEntry) { Font.Name = styleEntry.FontName.ToString(); Font.Color.SetColor(styleEntry.Color); Font.Bold = styleEntry.Style.HasFlag(styleTextTypeEnum.bold); Font.Italic = styleEntry.Style.HasFlag(styleTextTypeEnum.italic); Font.Strike = styleEntry.Style.HasFlag(styleTextTypeEnum.striketrough); Font.UnderLine = styleEntry.Style.HasFlag(styleTextTypeEnum.underline); Font.Size = styleEntry.FontSize; }
private (double height, double width) measureText(string text, ExcelFont excelFont, int width) { if (string.IsNullOrEmpty(text)) { return(0, 0); } Font drawFont = null; SolidBrush drawBrush = null; Graphics drawGraphics = null; Bitmap textBitmap = null; try { // start with empty bitmap, get it's graphic's object // and choose a font textBitmap = new Bitmap(1, 1); drawGraphics = Graphics.FromImage(textBitmap); drawFont = new Font(excelFont.Name, excelFont.Size); width = (int)(width * 7.5) * text.Length; // see how big the text will be var size = drawGraphics.MeasureString(text, drawFont, width); //// recreate the bitmap and graphic object with the new size //textBitmap = new Bitmap(textBitmap, Width, Height); //drawGraphics = Graphics.FromImage(textBitmap); //// get the drawing brush and where we're going to draw //drawBrush = new SolidBrush(Color.Black); //PointF DrawPoint = new PointF(0, 0); // draw //// clear the graphic white and draw the string //DrawGraphics.Clear(Color.White); //DrawGraphics.DrawString(TheText, DrawFont, DrawBrush, DrawPoint); //TextBitmap.Save("bmp2\\" + Guid.NewGuid().ToString() + ".bmp"); return(size.Height, size.Width); } finally { // don't dispose the bitmap, the caller needs it. drawFont?.Dispose(); drawBrush?.Dispose(); drawGraphics?.Dispose(); } }
public static void Save2Xlsx(string fileName, Dictionary <string, List <Order> > order_dic) { Workbook wb = new Workbook(); //清除默认的工作表 wb.Worksheets.Clear(); List <String> sheetlist = new List <String>(); //int no = order_dic.Count; int no = 1; foreach (string key in order_dic.Keys) { List <Order> orderlist = order_dic[key]; string sn = orderlist.First().Client; string sheet_name = sn; if (sn.Length >= 5) { sheet_name = sn.Substring(0, 5); } if (sheetlist.IndexOf(sn) > -1) { int _index = sheetlist.FindAll((String str) => str == sn).Count; //重复客户,页签名+1 sheet_name += _index.ToString(); } sheetlist.Add(sn); Worksheet st = wb.Worksheets.Add(sheet_name); //创建样式 CellStyle style = wb.Styles.Add("newStyle"); style.Font.FontName = "宋体"; style.Font.Size = 11; st.ApplyStyle(style); //创建字体 ExcelFont font1 = wb.CreateFont(); font1.FontName = "宋体"; font1.IsBold = true; font1.Size = 14; font1.Underline = FontUnderlineType.Single; Console.WriteLine("no:{0} key:{1} sheet_name:{2}", no, key, sheet_name); //foreach (Order order in orderlist) //{ // Console.WriteLine("key:{0} Model:{1} Num:{2}", key, order.Model, order.Num); //} FormatXlsx(no, st, font1, orderlist); no++; Thread.Sleep(5); } wb.SaveToFile(fileName + "_销售出库单.xlsx", FileFormat.Version2013); }
private static double GetRenderedTextHeight(string text, ExcelFont font, double width) { using (var bm = new Bitmap(1, 1)) using (var graphics = Graphics.FromImage(bm)) { var pixelWidth = Convert.ToInt32(width * 7.5); using (var drawingFont = new Font(font.Name, font.Size)) { var size = graphics.MeasureString(text, drawingFont, pixelWidth); return(Math.Min(Convert.ToDouble(size.Height) * 72 / 96, 409) * 1.2d); } } }
/// <summary> /// 设置表头行的字体样式 /// </summary> protected virtual ExcelFont SetColumnHeadFontStyle(ExcelFont font) { font.Bold = true; //加粗 font.Color.SetColor(Color.Black); //字体颜色 font.Name = "微软雅黑"; //字体名 font.Size = 12; //字体大小 font.Italic = false; //斜体 font.Strike = false; //删除线 font.UnderLine = false; //下划线 //font.UnderLineType = ExcelUnderLineType.Single;//下划线样式 //font.VerticalAlign = ExcelVerticalAlignmentFont.None;//字体垂直对齐 return(font); }
public void CreateFile(Window owner) { try { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; DataTable data = GetData(); ExcelFont fontBold = workbook.CreateFont(); sheet.PageSetup.Orientation = PageOrientationType.Landscape; fontBold.IsBold = true; var headers = data.AsEnumerable() .Select(s => s.Field <string>("Name")) .ToArray(); var cellsData = data.AsEnumerable() .Select(s => s.Field <int>("Type")) .ToArray(); int columnsRange = 2 + headers.Length - 1; sheet.InsertArray(headers, 2, 2, false); sheet.InsertArray(cellsData, 3, 2, false); CellRange range = sheet.Range[2, 2, 2, columnsRange]; range.Style.Rotation = 60; range.Style.Font.IsBold = true; range.AutoFitRows(); range.ColumnWidth = 4; sheet.Range[2, 2, 3, columnsRange].BorderInside(LineStyleType.Thin, Color.Black); sheet.Range[2, 2, 3, columnsRange].BorderAround(LineStyleType.Thin, Color.Black); workbook.SaveToFile(FileName, GetFileFormat()); System.Windows.MessageBox.Show(owner, "File has been created successfully.", "Information", MessageBoxButton.OK, MessageBoxImage.Information); workbook.SaveToFile("file.xps", FileFormat.XPS); PrintDialog dlg = new PrintDialog(); XpsDocument xpsDoc = new XpsDocument(@"file.xps", System.IO.FileAccess.Read); dlg.PrintDocument(xpsDoc.GetFixedDocumentSequence().DocumentPaginator, "Document title"); //PrintFile(owner, "file.xps"); } catch (Exception ex) { System.Windows.MessageBox.Show(owner, string.Format("An error has occurred while creating document: {0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error); } }
private void footerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("foot"); ExcelBorder border = getBorder(); if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont("Arial", 10); font.Bold = true; if (headerTextColor != "FF000000") { font.Color = headerTextColor; } for (uint row = 1; row <= cols.Length; row++) { uint rowInd = (uint)(row + headerOffset); // sheet.Rows[rowInd].Height = 22.5; for (uint col = 1; col <= cols[row - 1].Length; col++) { if (bgColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = bgColor; } sheet.Cells[rowInd, col].Style.Font = font; //TODO add text color, vertical alignment, horizontal alignment sheet.Cells[rowInd, col].Style.Border = border; sheet.Cells[rowInd, col].Value = cols[row - 1][col - 1].GetName(); } } /* for (int col = 0; col < cols.Length; col++) { * for (int row = 0; row < cols[col].Length; row++) { * int cspan = cols[col][row].GetColspan(); * if (cspan > 0) { * sheet.mergeCells(row, headerOffset + col, row + cspan - 1, headerOffset + col); * } * int rspan = cols[col][row].GetRowspan(); * if (rspan > 0) { * sheet.mergeCells(row, headerOffset + col, row, headerOffset + col + rspan - 1); * } * } * }*/ } headerOffset += cols.Length; }
internal void ToExcelFont(ExcelFont excelFont) { if (Color != Color.Empty) { excelFont.Color.SetColor(Color); } excelFont.UnderLineType = UnderLineType; excelFont.VerticalAlign = VerticalAlign; excelFont.Bold = IsBold; excelFont.Family = Family; excelFont.Italic = IsItalic; excelFont.Size = Size; excelFont.Strike = IsStrike; excelFont.UnderLine = IsUnderLine; excelFont.Name = Name; }
internal static CssDeclaration ToCss(this ExcelFont excelFont) { var css = new CssDeclaration(); if (excelFont.Bold) { css["font-weight"] = "bold"; } css["font-family"] = excelFont.Name; css["font-size"] = excelFont.Size + "pt"; css["color"] = excelFont.Color.ToHexCode(); return(css); }
public static double MeasureTextHeight(string text, ExcelFont font, int width) { if (string.IsNullOrEmpty(text)) { return(0.0); } var bitmap = new Bitmap(1, 1); var graphics = Graphics.FromImage(bitmap); var pixelWidth = Convert.ToInt32(width * 7.5); //7.5 pixels per excel column width var drawingFont = new Font(font.Name, font.Size); var size = graphics.MeasureString(text, drawingFont, pixelWidth); //72 DPI and 96 points per inch. Excel height in points with max of 409 per Excel requirements. return(Math.Min(Convert.ToDouble(size.Height) * 72 / 96, 409)); }
private void watermarkPrint(ExcelXmlParser parser) { if (watermark == null) { return; } ExcelFont font = wb.CreateFont(FontFamily, WatermarkFontSize); font.Bold = true; font.Color = WatermarkTextColor; ExcelBorder border = getBorder(); // f.setAlignment(Alignment.CENTRE); sheet.Cells[(uint)(headerOffset + 1), 0].Value = watermark; }
private void watermarkPrint(ExcelXmlParser parser) { if (watermark == null) { return; } ExcelFont font = wb.CreateFont("Arial", 10); font.Bold = true; font.Color = watermarkTextColor; ExcelBorder border = getBorder(); //f.setAlignment(Alignment.CENTRE); sheet.Cells[(uint)(headerOffset + 1), 0].Value = watermark; // Label label = new Label(0, headerOffset, watermark , f); // sheet.addCell(label); // sheet.mergeCells(0, headerOffset, colsNumber, headerOffset);*/ }
private double MeasureTextHeight(string text, ExcelFont font, double width) { if (string.IsNullOrEmpty(text)) { return(0.0); } var bitmap = new Bitmap(1, 1); var graphics = Graphics.FromImage(bitmap); var pixelWidth = Convert.ToInt32(width * 7); //7 pixels per excel column width var fontSize = font.Size * 1.01f; var drawingFont = new Font(font.Name, fontSize); var size = graphics.MeasureString(text, drawingFont, pixelWidth, new StringFormat { FormatFlags = StringFormatFlags.MeasureTrailingSpaces }); //72 DPI and 96 points per inch. Excel height in points with max of 409 per Excel requirements. return(Math.Min(Convert.ToDouble(size.Height) * 72 / 96, 409)); }
// Excel formatting can be at the entire cell level (e.g. the entire cell is marked italic) // or at the text level (e.g. some words in the cell are marked italic). // We detect and import both types, but if the user mixes levels for the same formatting type // e.g.selects the entire cell, bolds it, then selected some text within the cell and unbolds it, // we may get weird results, so we should tell users to use text-level formatting only /// <param name="formattingText">Has any text-level formatting we want this run to have. Text content does not matter.</param> /// <param name="cellFormatting">Has any cell-level formatting we want this run to have.</param> /// <param name="text">The text content of this run</param> /// <param name="stringBuilder">The string builder to which we are adding the xmlstring of this run private static void AddRunToXmlString(ExcelRichText formattingText, ExcelFont cellFormatting, string text, StringBuilder stringBuilder) { if (text.Length == 0) { return; } List <string> endTags = new List <string>(); if (formattingText.Bold || cellFormatting.Bold) { addTags("strong", endTags); } if (formattingText.Italic || cellFormatting.Italic) { addTags("em", endTags); } if (formattingText.UnderLine || cellFormatting.UnderLine) { addTags("u", endTags); } if (formattingText.VerticalAlign == ExcelVerticalAlignmentFont.Superscript || cellFormatting.VerticalAlign == ExcelVerticalAlignmentFont.Superscript) { addTags("sup", endTags); } stringBuilder.Append(text); endTags.Reverse(); foreach (var endTag in endTags) { stringBuilder.Append(endTag); } void addTags(string tagName, List <string> endTag) { stringBuilder.Append("<" + tagName + ">"); endTag.Add("</" + tagName + ">"); } }
public Font(ExcelFont font) { _font = font; string hexARGB = _font.Color.Rgb; if (string.IsNullOrEmpty(hexARGB)) { _fontColor = (Color)Color.BLACK_COLOR; } else { string hexA = hexARGB.Substring(0, 2); string hexR = hexARGB.Substring(2, 2); string hexG = hexARGB.Substring(4, 2); string hexB = hexARGB.Substring(6, 2); int a = 0, r = 0, g = 0, b = 0; a = int.Parse(hexA, System.Globalization.NumberStyles.HexNumber); r = int.Parse(hexR, System.Globalization.NumberStyles.HexNumber); g = int.Parse(hexG, System.Globalization.NumberStyles.HexNumber); b = int.Parse(hexB, System.Globalization.NumberStyles.HexNumber); _fontColor = new Color(a, r, g, b); } _fontColor.OnValueChanged += ColorChanged; }
private void AssertFont(string expectedName, int expectedSize, FontStyle expectedStyle, Color expectedColor, ExcelFont font) { Assert.AreEqual(expectedName, font.Font.FontFamily.Name); Assert.AreEqual(expectedSize, font.Font.Size); Assert.AreEqual(expectedStyle, font.Font.Style); Assert.AreEqual(expectedColor, font.FontColor); }
public static void FormatXlsx(int no, Worksheet st, ExcelFont font, List <Order> orderList) { string no_str = no.ToString().PadLeft(4, '0'); Order order = orderList.First(); string date = order.Date.Replace('年', '-').Replace('月', '-').Replace('日', ' ').Trim(); //设置列宽 st.Columns[0].ColumnWidth = 11F; st.Columns[1].ColumnWidth = 14F; st.Columns[2].ColumnWidth = 15F; st.Columns[3].ColumnWidth = 10F; st.Columns[4].ColumnWidth = 12F; st.Columns[5].ColumnWidth = 9F; st.Columns[6].ColumnWidth = 11F; //横向合并A1到G1的单元格 st.Range["A1:G1"].Merge(); st.Rows[0].RowHeight = 22F; //写入数据到A1单元格,设置文字格式及对齐方式 //为A1单元格写入数据并设置字体 RichText richText = st.Range["A1"].RichText; richText.Text = "出入库单"; st.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center; st.Range["A1"].VerticalAlignment = VerticalAlignType.Center; richText.SetFont(0, richText.Text.ToArray().Length - 1, font); st.Range["E2"].Value = "页码:"; st.Range["E2"].HorizontalAlignment = HorizontalAlignType.Right; st.Range["F2"].Value = "第1页,共1页"; st.Range["F2"].HorizontalAlignment = HorizontalAlignType.Left; st.Range["F2:G2"].Merge(); st.Range["A3"].Value = "日期:"; st.Range["A3"].HorizontalAlignment = HorizontalAlignType.Right; st.Range["B3"].Value = date; st.Range["B3"].HorizontalAlignment = HorizontalAlignType.Left; st.Range["E3"].Value = "单号:"; st.Range["E3"].HorizontalAlignment = HorizontalAlignType.Right; st.Range["F3"].Value = "I0-" + date + "-" + no_str; st.Range["F3:G3"].Merge(); st.Range["A4"].Value = "客户名称:"; st.Range["A4"].HorizontalAlignment = HorizontalAlignType.Right; st.Range["B4"].Value = order.Client; st.Range["B4"].HorizontalAlignment = HorizontalAlignType.Left; st.Range["E4"].Value = "单据类型:"; st.Range["E4"].HorizontalAlignment = HorizontalAlignType.Right; st.Range["F4"].Value = "销售出库单"; st.Range["F4"].HorizontalAlignment = HorizontalAlignType.Left; st.Range["B4:D4"].Merge(); st.Range["F4:G4"].Merge(); //创建一个DataTable DataTable dt1 = new DataTable(); dt1.Columns.Add("序号"); dt1.Columns.Add("货品名称"); dt1.Columns.Add("规格"); dt1.Columns.Add("单位"); dt1.Columns.Add("数量"); dt1.Columns.Add("备注"); int length = orderList.Count; int i = 1; int total = 0; foreach (Order o in orderList) { dt1.Rows.Add(i.ToString(), o.Name, o.Model, o.Unit, o.Num, ""); total += Convert.ToInt16(o.Num); i++; } int j = 8 - length; if (j > 0) { while (j-- > 0) { dt1.Rows.Add(""); } } for (int k = 5; k < dt1.Rows.Count + 7; k++) { st.Range["F" + k + ":G" + k].Merge(); } //Console.WriteLine("dt1.Rows.Count:{0}", dt1.Rows.Count); int index = dt1.Rows.Count + 6; st.Range["A" + index + ":" + "D" + index].Merge(); st.Range["A" + index + ":" + "D" + index].HorizontalAlignment = HorizontalAlignType.Right; dt1.Rows.Add("合计:", "", "", "", total.ToString()); st.Range["A5:G" + (index - 1)].HorizontalAlignment = HorizontalAlignType.Center; st.Range["A" + index].HorizontalAlignment = HorizontalAlignType.Right; st.Range["E" + index].HorizontalAlignment = HorizontalAlignType.Center; //设置网格线样式及颜色 st.Range["A5:G" + index].BorderAround(LineStyleType.Thin); st.Range["A5:G" + index].BorderInside(LineStyleType.Thin); st.Range["A5:G" + index].Borders.KnownColor = ExcelColors.Black; //将DataTable数据写入工作表 st.InsertDataTable(dt1, true, 5, 1, true); st.Range["A" + (index + 2)].Value = "审核:"; st.Range["A" + (index + 2)].HorizontalAlignment = HorizontalAlignType.Right; st.Range["B" + (index + 2)].Value = "陈蓉"; st.Range["C" + (index + 2)].Value = "发货:"; st.Range["C" + (index + 2)].HorizontalAlignment = HorizontalAlignType.Right; st.Range["D" + (index + 2)].Value = "陆海"; st.Range["F" + (index + 2)].Value = "制单:"; st.Range["F" + (index + 2)].HorizontalAlignment = HorizontalAlignType.Right; st.Range["G" + (index + 2)].Value = "赵静"; }
private void rowsPrint(ExcelXmlParser parser, Stream resp) { ExcelRow[] rows = parser.getGridContent(); this.rows_stat = rows.Length; ExcelBorder border = getBorder(); ExcelFont font = wb.CreateFont("Arial", 10); // if (gridTextColor != "FF000000") // font.Color = gridTextColor; for (uint row = 1; row <= rows.Length; row++) { ExcelCell[] cells = rows[row - 1].getCells(); uint rowInd = (uint)(row + headerOffset); // sheet.Rows[rowInd].Height = 20; for (uint col = 1; col <= cells.Length; col++) { if (cells[col - 1].GetBold() || cells[col - 1].GetItalic()) { ExcelFont curFont = wb.CreateFont("Arial", 10);; // if (gridTextColor != "FF000000") // font.Color = gridTextColor; if (cells[col - 1].GetBold()) { font.Bold = true; } if (cells[col - 1].GetItalic()) { font.Italic = true; } sheet.Cells[rowInd, col].Style.Font = curFont; } else { sheet.Cells[rowInd, col].Style.Font = font; } sheet.Cells[rowInd, col].Style.Border = border; if ((!cells[col - 1].GetBgColor().Equals("")) && (parser.getProfile().Equals("full_color"))) { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = "FF" + cells[col - 1].GetBgColor(); } else { //Colour bg; if (row % 2 == 0 && scaleTwoColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = scaleTwoColor; } else { if (scaleOneColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = scaleOneColor; } } } int intVal; double dbVal; if (int.TryParse(cells[col - 1].GetValue(), out intVal)) { sheet.Cells[rowInd, col].Value = intVal; } else if (double.TryParse(cells[col - 1].GetValue(), out dbVal)) { sheet.Cells[rowInd, col].Value = dbVal; } else { sheet.Cells[rowInd, col].Value = cells[col - 1].GetValue(); } //COLOR! //String al = cells[row].getAlign(); //if (al == "") // al = cols[0][row].getAlign(); //if (al.equalsIgnoreCase("left")) //{ // f.setAlignment(Alignment.LEFT); //} //else //{ // if (al.equalsIgnoreCase("right")) // { // f.setAlignment(Alignment.RIGHT); // } // else // { // f.setAlignment(Alignment.CENTRE); // } //} } } headerOffset += rows.Length; }
//public void Generate(string xml, HttpResponseBase resp) //{ // var data = new MemoryStream(); // resp.ContentType = ContentType; // resp.HeaderEncoding = Encoding.UTF8; // resp.AppendHeader("Content-Disposition", "attachment;filename=my_report.xlsx"); // resp.AppendHeader("Cache-Control", "max-age=0"); // Generate(xml, data); // data.WriteTo(resp.OutputStream); //} private void headerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("head"); int[] widths = parser.getWidths(); this.cols_stat = widths.Length; int sumWidth = 0; for (int i = 0; i < widths.Length; i++) { sumWidth += widths[i]; } if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont("Arial", 10); font.Bold = true; if (headerTextColor != "FF000000") { font.Color = headerTextColor; } ExcelBorder border = getBorder(); for (uint row = 1; row <= cols.Length; row++) { // sheet.Rows[row].Height = 22.5; for (uint col = 1; col <= cols[row - 1].Length; col++) { // trick before font //sheet.Cells[row, col].Style.SetAlignmentCenter = true; sheet.Cells[row, col, true].IsHead = true; sheet.Cells[row, col, true].Style.Font = font;//if bold font assigned after border - all table will be bold, weird, find out later sheet.Cells[row, col].Style.Border = border; sheet.Columns[col].Width = widths[col - 1] / scale; String name = cols[row - 1][col - 1].GetName(); if (bgColor != "FFFFFFFF") { sheet.Cells[row, col, true].Style.Fill.ForegroundColor = bgColor; } ///TODO: ///font color, merge cells, alignment sheet.Cells[row, col, true].Value = name; colsNumber = (int)col; } } headerOffset = cols.Length; /* for (int col = 0; col < cols.Length; col++) { * for (int row = 0; row < cols[col].Length; row++) { * int cspan = cols[col][row].GetColspan(); * if (cspan > 0) { * sheet.mergeCells(row, col, row + cspan - 1, col); * } * int rspan = cols[col][row].GetRowspan(); * if (rspan > 0) { * sheet.mergeCells(row, col, row, col + rspan - 1); * } * } * }*/ } }