private void exportXLSX_Click(object sender, RoutedEventArgs e)
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
// XSSFWorkbook : *.xlsx >= Excel2007
// HSSFWorkbook : *.xls < Excel2007
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet Name");
// 所有索引都从0开始
// 合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex); //创建行
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content");
sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
rowIndex++;
// 插入图片
byte[] data = File.ReadAllBytes(@"image.jpg");
int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor;
anchor.Col1 = 10;
anchor.Row1 = 0;
XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict.Resize();
// 新建sheet
var sheet2 = workbook.CreateSheet("Sheet2");
// 更改样式
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
//保存
workbook.Write(fs);
}
txtStatus.Text = "writing xlsx successful!";
}
private static void CreateChartDataSheet(Stream stream, IWorkbook workbook)
{
var sheet = workbook.CreateSheet("Chart");
var drawing = sheet.CreateDrawingPatriarch();
var data = new byte[stream.Length];
stream.Read(data, 0, (int)stream.Length);
var picInd = workbook.AddPicture(data, PictureType.PNG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFClientAnchor anchor = helper?.CreateClientAnchor() as XSSFClientAnchor;
if (anchor != null)
{
anchor.Col1 = 0;
anchor.Row1 = 0;
var pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict?.Resize();
}
}
public IClientAnchor CreateClientAnchor()
{
return(helper.CreateClientAnchor());
}
public void TestCommentShowsBox()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFCell cell = (XSSFCell)sheet.CreateRow(0).CreateCell(0);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFCreationHelper factory = (XSSFCreationHelper)wb.GetCreationHelper();
XSSFClientAnchor anchor = (XSSFClientAnchor)factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 3;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 5;
XSSFComment comment = (XSSFComment)drawing.CreateCellComment(anchor);
XSSFRichTextString str = (XSSFRichTextString)factory.CreateRichTextString("this is a comment");
comment.String = str;
cell.CellComment = comment;
XSSFVMLDrawing vml = sheet.GetVMLDrawing(false);
CT_Shapetype shapetype = null;
ArrayList items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
using (MemoryStream ws = new MemoryStream())
{
wb.Write(ws);
using (MemoryStream rs = new MemoryStream(ws.GetBuffer()))
{
wb = new XSSFWorkbook(rs);
sheet = (XSSFSheet)wb.GetSheetAt(0);
vml = sheet.GetVMLDrawing(false);
shapetype = null;
items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
//wb.Write(new FileStream("comments.xlsx", FileMode.Create));
//using (MemoryStream ws2 = new MemoryStream())
//{
// vml.Write(ws2);
// throw new System.Exception(System.Text.Encoding.UTF8.GetString(ws2.GetBuffer()));
//}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
}
}
}
