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);
* }
* }
* }*/
}
}