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);
}
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);
}
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);
}
/// <summary>
/// 设置字体
/// </summary>
private void SetFont(ICellStyle Style, bool IsHead = true)
{
IFont Font = Workbook.CreateFont();
Font.Color = IsHead ? HSSFColor.Red.Index : HSSFColor.Black.Index;
Font.IsBold = IsHead ? true : false;
Style.SetFont(Font);
}
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);
}
}
private IFont GetHeaderFont(short fontSize = 12)
{
var font = (XSSFFont)Workbook.CreateFont();
font.FontHeightInPoints = fontSize;
font.FontName = "Calibri";
font.Boldweight = (short)FontBoldWeight.Bold;
return(font);
}
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);
}
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);
}
}
protected ISheet CreateColumnHeader(DataTable dataSource, string sheetName)
{
ISheet sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));
/* styling cell */
ICellStyle headerStyle = this.Workbook.CreateCellStyle();
headerStyle.BorderBottom = BorderStyle.THIN;
headerStyle.BorderLeft = BorderStyle.THIN;
headerStyle.BorderRight = BorderStyle.THIN;
headerStyle.BorderTop = BorderStyle.THIN;
IFont font = Workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = short.MaxValue;//Bold
headerStyle.SetFont(font);
headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index;
headerStyle.FillPattern = FillPatternType.THIN_HORZ_BANDS;
headerStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index;
headerStyle.WrapText = true;
headerStyle.VerticalAlignment = VerticalAlignment.CENTER;
headerStyle.Alignment = HorizontalAlignment.CENTER;
IFont headerLabelFont = this.Workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerStyle.SetFont(headerLabelFont);
IRow row = sheet.CreateRow(0);
for (int colIndex = 0; colIndex < dataSource.Columns.Count; colIndex++)
{
ICell cell = row.CreateCell(colIndex);
cell.SetCellValue(dataSource.Columns[colIndex].ColumnName);
if (headerStyle != null)
{
cell.CellStyle = headerStyle;
}
}
return(sheet);
}
protected override void BuildTitle()
{
TempRow = Sheet.CreateRow(NextRowIndex);
TempCell = TempRow.CreateCell(0);
TempCell.CellStyle = HeaderStyle;
TempCell.SetCellValue("海淀区行政事业单位国有资产处理明细表");
//设置单元格的高度
TempRow.Height = 30 * 20;
IFont font = Workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 19;
font.Boldweight = (short)FontBoldWeight.Bold;
TempCell.CellStyle.SetFont(font);
Sheet.AddMergedRegion(new CellRangeAddress(NextRowIndex, NextRowIndex, 0, 10));
NextRowIndex++;
}
public ExportDoAssetsBase(IAssetsOptionService assetsOptionService, int doAssetsId) : base()
{
AssetsOptionService = assetsOptionService;
DoAssetsId = doAssetsId;
HeaderStyle = Workbook.CreateCellStyle();
HeaderStyle.Alignment = HorizontalAlignment.Center;
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = Workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
CommCellStyle = Workbook.CreateCellStyle();
CommCellStyle.VerticalAlignment = VerticalAlignment.Center;
CommCellStyle.WrapText = true;
FooterCellStyle = Workbook.CreateCellStyle();
FooterCellStyle.Alignment = HorizontalAlignment.Center;
FooterCellStyle.VerticalAlignment = VerticalAlignment.Center;
FooterCellStyle.WrapText = true;
}
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 );
}
private Font GetFont(ContentStyle style)
{
var curFont = Workbook.GetFontAt(0);
for (short i = 1; i < Workbook.NumberOfFonts; i++)
{
var font = Workbook.GetFontAt(i);
if (style.HasFontName())
{
if (!String.Equals(font.FontName, style.FontName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
}
else
if (!String.Equals(font.FontName, curFont.FontName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (style.HasFontStyle())
{
if (style.FontStyle.HasFlag(FontStyle.Italic) && !font.IsItalic)
{
continue;
}
if (style.FontStyle.HasFlag(FontStyle.Bold) && font.Boldweight != (short)FontBoldWeight.BOLD)
{
continue;
}
if (style.FontStyle.HasFlag(FontStyle.Underline) && font.Underline != (byte)FontUnderlineType.SINGLE)
{
continue;
}
if (style.FontStyle == FontStyle.Regular &&
(font.IsItalic || font.Boldweight != (short)FontBoldWeight.NORMAL || font.Underline != (byte)FontUnderlineType.NONE))
{
continue;
}
}
if (style.FontDSize == 0 && curFont.FontHeightInPoints != font.FontHeightInPoints)
{
continue;
}
if (style.FontDSize != 0)
{
var fs = curFont.FontHeightInPoints + style.FontDSize;
if (font.FontHeightInPoints != fs)
{
continue;
}
}
if (style.HasFontColor() && style.FontColor != font.Color)
{
continue;
}
return(font);
}
return(Workbook.CreateFont());
}
