public void TestBug56835CellComment()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
// first comment works
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment;
Assert.IsNotNull(comment);
try
{
Drawing.CreateCellComment(anchor);
Assert.Fail("Should fail if we try to add the same comment for the same cell");
}
catch (ArgumentException)
{
// expected
}
}
finally
{
wb.Close();
}
}
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!";
}
/*泛类型行不通
* public ISheet pictureDataToSheet<T>(ISheet sheet, T pictureNPOI,int startRow,int startCol, int endRow,int endCol)
* // where T: XSSFPicture, HSSFPicture,类型应该只有一种的原因吧，无法执行类型约束为两个类，因为类的约束必须放在第一个
* {
*
* XSSFPicture pictureNPOI_XSSFPicture = pictureNPOI as XSSFPicture;
* HSSFPalette pictureNPOI_HSSFPalette = pictureNPOI as HSSFPalette;
* //XSSFPicture，HSSFPalette是类，只能有一种类型，正好是泛类型要解决的
* //方法和使用一样，但是T的类型取决类申城的Isheet的类型
* //应该使用重载
* if (true)
* {
*
* }
* else
* {
* return null;
* }
* workbook.AddPicture(pictureNPOI.)
* }
*/
//重载
public ISheet pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int startRow, int startCol, int endRow, int endCol)
{
/*将实际图片转换为pictureData时使用，但是pictureNPOI本身就是picture
* byte[] pictureByte=
* workbook.AddPicture(, PictureType.PNG);
*/
//判断是否有sheet
//无，则创建
if (sheet == null)
{
sheet = this.workbook.CreateSheet();
}
//执行向sheet写图片
//创建DrawingPatriarch，存放的容器
IDrawing patriarch = sheet.CreateDrawingPatriarch();
///System.InvalidCastException:“无法将类型为“NPOI.XSSF.UserModel.XSSFDrawing”的对象强制转换为类型“NPOI.HSSF.UserModel.HSSFPatriarch”。”
/// HSSFPatriarch patriarch = (HSSFPatriarch)sheetA.CreateDrawingPatriarch();
/// 根据报错改为如下
// IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, startCol, startRow, endCol, endRow);
//将图片文件读入workbook，用索引指向该文件
int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG);
//根据读入图片和anchor把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
return(sheet);
}
public void Create()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = AnchorType.MoveDontResize;
//load the picture and get the picture index in the workbook
//first picture
int imageId = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
picture.Resize();
//second picture
int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
picture.LineStyle = LineStyle.DashDotGel;
using (FileStream sw = File.Create("test.xlsx"))
{
workbook.Write(sw);
}
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int row, int column, int rowEnd, int columnEnd)
{
try
{
if (_myExcel is HSSFWorkbook)
{
HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
else if (_myExcel is XSSFWorkbook)
{
XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
}
catch (Exception exception)
{
//Log.Write.Error("InsertPictures to file fail", exception);
}
}
public static byte[] ExportByTemplateOption2(string templateFileName, ExcellExportOption option, List <SetExcelDto> setList, int insertPicRowIndex, byte[] picBytes, int insertTableRowIndex, DataTable table)
{
var workbook = OpenExcel(templateFileName);
var sheet = workbook.GetSheetAt(0);
FillSheet(sheet, table, insertTableRowIndex);
SetupByOption(workbook, option);
//指定单元格赋值
if (setList != null)
{
SetExcelValue(workbook, setList);
}
//插入图片
if (picBytes != null)
{
int pictureIndex = workbook.AddPicture(picBytes, PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(50, 50, 225, 225, 0, insertPicRowIndex, 1, insertPicRowIndex + 5);
IPicture pict = patriarch.CreatePicture(anchor, pictureIndex);
}
byte[] data = null;
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
data = ms.ToArray();
ms.Close();
ms.Dispose();
}
workbook.Close();
return(data);
}
/// <summary>
/// 向sheet插入图片
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="sheet">工作表</param>
/// <param name="filePath">文件路径</param>
/// <param name="row">第几行</param>
/// <param name="col">第几列</param>
/// <param name="suffix">后缀</param>
private static void AddPicture(XSSFWorkbook workbook, ISheet sheet, string filePath, int row, int col, string suffix)
{
try
{
if (!string.IsNullOrEmpty(filePath))
{
//转为本地路径
filePath = HttpContext.Current.Server.MapPath(filePath);
if (File.Exists(filePath))
{
var bytes = File.ReadAllBytes(filePath);
if (bytes.Length > 0)
{
var imagedId = 0;
var pictureType = new PictureType();
if (suffix.Equals("png", StringComparison.OrdinalIgnoreCase))
{
pictureType = PictureType.PNG;
}
else if (suffix.Equals("jpg", StringComparison.OrdinalIgnoreCase))
{
pictureType = PictureType.JPEG;
}
else
{
pictureType = PictureType.JPEG;
}
var patriarch = sheet.CreateDrawingPatriarch();
imagedId = workbook.AddPicture(bytes, pictureType);
//处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，第三个参数为宽，第四个参数为高
var anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
//dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
//dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
//dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
//dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
anchor.AnchorType = AnchorType.MoveDontResize;
//anchor.AnchorType = 3;
//load the picture and get the picture index in the workbook
var picture = (XSSFPicture)patriarch.CreatePicture(anchor, imagedId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set. 这句话一定不要，这是用图片原始大小来显示
//picture.Resize(0.5);
picture.LineStyle = LineStyle.DashDotGel;
}
}
}
}
catch (Exception ex)
{
LogUtil.WriteException(ex);
//此处采用跳过的方式
//throw ex;
}
}
public List <object> GetShapes(ISheet sheet)
{
List <object> list = new List <object>();
IDrawing drawingPatriarch = sheet.DrawingPatriarch;
if (sheet is HSSFSheet)
{
HSSFShapeContainer hSSFShapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
if (drawingPatriarch == null)
{
return(list);
}
IList <HSSFShape> children = hSSFShapeContainer.Children;
using (IEnumerator <HSSFShape> enumerator = children.GetEnumerator())
{
while (enumerator.MoveNext())
{
HSSFShape current = enumerator.Current;
if (current != null && current.Anchor is HSSFClientAnchor)
{
HSSFClientAnchor hSSFClientAnchor = current.Anchor as HSSFClientAnchor;
if (this.IsInternalOrIntersect(this.ShapeRange.MinRow, this.ShapeRange.MaxRow, this.ShapeRange.MinColumn, this.ShapeRange.MaxColumn, hSSFClientAnchor.Row1, hSSFClientAnchor.Row2, hSSFClientAnchor.Col1, hSSFClientAnchor.Col2, true))
{
list.Add(current);
}
}
}
return(list);
}
}
List <POIXMLDocumentPart> relations = (sheet as XSSFSheet).GetRelations();
foreach (POIXMLDocumentPart current2 in relations)
{
if (current2 is XSSFDrawing)
{
XSSFDrawing xSSFDrawing = (XSSFDrawing)current2;
List <XSSFShape> shapes = xSSFDrawing.GetShapes();
foreach (XSSFShape current3 in shapes)
{
XSSFAnchor anchor = current3.GetAnchor();
if (current3 != null && anchor is XSSFClientAnchor)
{
XSSFClientAnchor xSSFClientAnchor = anchor as XSSFClientAnchor;
if (this.IsInternalOrIntersect(this.ShapeRange.MinRow, this.ShapeRange.MaxRow, this.ShapeRange.MinColumn, this.ShapeRange.MaxColumn, xSSFClientAnchor.Row1, xSSFClientAnchor.Row2, xSSFClientAnchor.Col1, xSSFClientAnchor.Col2, true))
{
list.Add(current3);
}
}
}
}
}
return(list);
}
/// <summary>
/// XLSX类型Excle文件添加图片
/// </summary>
/// <param name="intRowIndex">插入图片的行数</param>
/// <param name="pictureIndex">图片的顺序</param>
private void XSSFCreatePicture(int intRowIndex, int pictureIndex)
{
XSSFDrawing xssfDrawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.Row1 = intRowIndex;
//anchor.AnchorType = AnchorType2;
XSSFPicture picture = (XSSFPicture)xssfDrawing.CreatePicture(anchor, pictureIndex);
picture.LineStyle = LineStyle.Solid;
picture.Resize();//显示图片的原始尺寸
}
private void setPic(XSSFWorkbook workbook, XSSFDrawing patriarch, string path, ISheet sheet, int rowline, int col, int rowMergerPlus = 0, int colMergerPlus = 0)
{
if (string.IsNullOrEmpty(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, rowline, col + colMergerPlus, rowline + rowMergerPlus);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
/// <summary>
/// 将Base64字符串转换为图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="bmp">图片</param>
/// <param name="col1">图片起始列</param>
/// <param name="row1">图片起始行</param>
/// <param name="col2">图片结束列</param>
/// <param name="row2">图片结束行</param>
/// <returns></returns>
public static void AddImageToExcel(ISheet sheet, Bitmap bmp, int col1, int row1, int col2, int row2)
{
string base64 = ImgToBase64String(bmp);
byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+"));
int pictureIdx = sheet.Workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//##处理照片位置，【图片左上角为（6, 2）第2+1行6+1列，右下角为（8, 6）第6+1行8+1列】
XSSFClientAnchor anchor = new XSSFClientAnchor(100 * 10000, 0, 100, 100, col1, row1, col2, row2);
patriarch.CreatePicture(anchor, pictureIdx);
}
public void TestAccessMethods()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30) as XSSFClientAnchor;
XSSFChart chart = Drawing.CreateChart(anchor) as XSSFChart;
XSSFCategoryAxis axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom) as XSSFCategoryAxis;
axis.Crosses = (AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public static IComment AddComment(IDrawing patr, ICell cell, IRow headerRow, string value)
{
var anchor = new XSSFClientAnchor();
anchor.Col1 = cell.ColumnIndex + 1;
anchor.Col2 = cell.ColumnIndex + 3;
anchor.Row1 = headerRow.RowNum + 1;
anchor.Row2 = headerRow.RowNum + 5;
IComment comment = patr.CreateCellComment(anchor);
comment.String = (new XSSFRichTextString(value));
return(comment);
}
/// <summary>
/// 将指定的图像数据绘制到工作表中
/// </summary>
/// <param name="workBook">工作簿对象</param>
/// <param name="sheet">工作表对象</param>
/// <param name="start">图像的起始单元格坐标</param>
/// <param name="width">图像占用的列的数量</param>
/// <param name="height">图像占用的行的数量</param>
/// <param name="graphData">图像包含的数据</param>
/// <param name="type">图像数据源图像的格式，默认使用PNG</param>
public void DrawGraphs(IWorkbook workBook, ISheet sheet,
Point start, Int32 width, Int32 height,
Byte[] graphData,
PictureType type = DefaultPictureType)
{
IClientAnchor anchor = null;
if (workBook is HSSFWorkbook)
{
anchor = new HSSFClientAnchor(0, 0, 0, 0, start.X, start.Y, start.X + width, start.Y + height);
}
else
{
anchor = new XSSFClientAnchor(0, 0, 0, 0, start.X, start.Y, start.X + width, start.Y + height);
}
this.DrawGraphs(workBook, sheet, anchor, graphData, type);
}
/// <summary>
/// .xlsx后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSXExcel(string excelPath, string imgPath)
{
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
{
//创建工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
//获取第一个工作表（下标从0起）
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
//获取指定图片的字节流
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG);
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(excelPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
xssfworkbook.Write(file);
//关闭工作簿
xssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
static void CreateChart(ISheet sheet)
{
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 3, 0, 10, 15);
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.Bottom;
IBarChartData <string, double> data = chart.ChartDataFactory.CreateBarChartData <string, double>();
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
bottomAxis.MajorTickMark = AxisTickMark.None;
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
leftAxis.SetCrossBetween(AxisCrossBetween.Between);
IChartDataSource <string> categories = DataSources.FromStringCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 0, 0));
// Abono
IChartDataSource <double> xAbono = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 1, 1));
// Isento
IChartDataSource <double> xIsento = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 2, 2));
// Violação
IChartDataSource <double> xViolacao = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 3, 3));
var s1 = data.AddSeries(categories, xAbono);
s1.SetTitle("Abono");
var s2 = data.AddSeries(categories, xIsento);
s2.SetTitle("Isento");
var s3 = data.AddSeries(categories, xViolacao);
s3.SetTitle("Violação");
chart.Plot(data, bottomAxis, leftAxis);
}
public void TestAddChartsToNewWorkbook()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s1 = (XSSFSheet)wb.CreateSheet();
XSSFDrawing d1 = (XSSFDrawing)s1.CreateDrawingPatriarch();
XSSFClientAnchor a1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
XSSFChart c1 = (XSSFChart)d1.CreateChart(a1);
Assert.AreEqual(1, d1.GetCharts().Count);
Assert.IsNotNull(c1.GetGraphicFrame());
Assert.IsNotNull(c1.GetOrCreateLegend());
XSSFClientAnchor a2 = new XSSFClientAnchor(0, 0, 0, 0, 1, 11, 10, 60);
XSSFChart c2 = (XSSFChart)d1.CreateChart(a2);
Assert.IsNotNull(c2);
Assert.AreEqual(2, d1.GetCharts().Count);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void IncrementShapeId()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
byte[] jpegData = Encoding.UTF8.GetBytes("picture1");
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape1 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)1, shape1.GetCTPicture().nvPicPr.cNvPr.id);
jpegData = Encoding.UTF8.GetBytes("picture2");
jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape2 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)2, shape2.GetCTPicture().nvPicPr.cNvPr.id);
}
public static ICell AddPicture(this ICell cell, byte[] bytes)
{
int pictureIdx = cell.Sheet.Workbook.AddPicture(bytes, PictureType.JPEG);
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
if (cell is HSSFCell)
{
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
XSSFClientAnchor anchor = new XSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
return(cell);
}
private static void InsertImage(XSSFWorkbook workbook, ISheet sheet, byte[] data, int row, int column)
{
try
{
int picInd = workbook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1)
{
AnchorType = AnchorType.MoveAndResize
};
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, picInd);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
}
catch
{
//ignore
}
}
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();
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage("../../image/HumpbackWhale.jpg", workbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public IClientAnchor GenerationClientAnchor(String filePath,
Int32 dx1, Int32 dy1, Int32 dx2, Int32 dy2, Int32 col1, Int32 row1, Int32 col2, Int32 row2)
{
IClientAnchor clientAnchor = null;
String ext = Path.GetExtension(filePath);
switch (ext)
{
case ExtOfExcel2003:
{
clientAnchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
}
break;
case ExtOfExcel2007Plus:
default:
{
clientAnchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
}
break;
}
return(clientAnchor);
}
public void TestReadAnchors()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4);
XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape;
Assert.IsNotNull(shape1);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5);
XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape;
Assert.IsNotNull(shape2);
int pictureIndex = wb1.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6);
XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape;
Assert.IsNotNull(shape3);
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(3, shapes.Count);
Assert.AreEqual(shapes[0].GetAnchor(), anchor1);
Assert.AreEqual(shapes[1].GetAnchor(), anchor2);
Assert.AreEqual(shapes[2].GetAnchor(), anchor3);
checkRewrite(wb2);
wb2.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
// Create a row and put some cells in it. Rows are 0 based.
IRow row;
ICell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
{
row = sheet.CreateRow((short)rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
{
cell = row.CreateCell((short)colIndex);
if (colIndex == 0)
{
cell.SetCellValue("X" + rowIndex);
}
else
{
var x = colIndex * (rowIndex + 1);
cell.SetCellValue(x * x + 2 * x + 1);
}
}
}
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 3, 3, 10, 12);
CreateChart(sheet, drawing, anchor, "s1", 0, 9, 1);
using (FileStream fs = File.Create("test.xlsx"))
{
wb.Write(fs);
}
}
/// <summary>
/// An Arrow bug from 2007+ version
/// </summary>
/// <param name="cell"></param>
/// <param name="comment"></param>
/// <param name="author"></param>
public void AddCellComment(ICell cell, string comment, string author = "")
{
var patriach = cell.Sheet.CreateDrawingPatriarch();
IComment ccm;
IRichTextString text;
if (IsNewFormat)
{
var anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 4, cell.ColumnIndex + 4);
ccm = patriach.CreateCellComment(anchor);
text = new XSSFRichTextString(comment);
}
else
{
var anchor = new HSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 4, cell.ColumnIndex + 4);
ccm = patriach.CreateCellComment(anchor);
text = new HSSFRichTextString(comment);
}
ccm.String = text;
ccm.Author = author;
ccm.Visible = false;
cell.CellComment = ccm;
}
public static MemoryStream Export(DataSet dataSet, IList <Image> jpgImagesList, Dictionary <bool, string> boolKeyValue, string fileExt)
{
DataSet ds = dataSet.Copy();
IWorkbook workbook;
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
switch (fileExt)
{
case ".xlsx":
workbook = new XSSFWorkbook();
break;
case ".xls":
workbook = new HSSFWorkbook();
break;
default:
workbook = null;
break;
}
if (workbook == null)
{
return(null);
}
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int tableIndex = -1;
for (int i = 0; i < 32; i++)
{
sheet.SetColumnWidth(i, 20 * 256);
}
switch (fileExt)
{
case ".xlsx":
{
if (jpgImagesList != null)
{
XSSFDrawing patriarch = sheet.CreateDrawingPatriarch() as XSSFDrawing;
foreach (Image image in jpgImagesList)
{
byte[] bytes = ConvertImage(image);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor {
Row1 = rowIndex
};
XSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx) as XSSFPicture;
if (pict != null)
{
pict.Resize();
}
rowIndex = anchor.Row2 + 2;
}
}
}
break;
case ".xls":
{
if (jpgImagesList != null)
{
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
foreach (Image image in jpgImagesList)
{
byte[] bytes = ConvertImage(image);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
HSSFClientAnchor anchor = new HSSFClientAnchor {
Row1 = rowIndex
};
HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx) as HSSFPicture;
if (pict != null)
{
pict.Resize();
}
rowIndex = anchor.Row2 + 2;
}
}
}
break;
}
foreach (DataTable dtSource in ds.Tables)
{
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex >= 60000)
{
sheet = workbook.CreateSheet();
rowIndex = 0;
}
if (rowIndex == 0 || tableIndex != ds.Tables.IndexOf(dtSource))
{
tableIndex = ds.Tables.IndexOf(dtSource);
//#region 表头及样式
//{
// if (dtSource.TableName.IndexOf("Table", StringComparison.Ordinal) != 0)
// {
// IRow headerRow = sheet.CreateRow(rowIndex);
// headerRow.HeightInPoints = dtSource.TableName.Length != new Regex(" ").Replace(dtSource.TableName, "\n", 1).Length ? 25 : 50;
// headerRow.CreateCell(0).SetCellValue(new Regex(" ").Replace(dtSource.TableName, "\n", 1));
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// //font.Color = HSSFColor.WHITE.index;
// headStyle.SetFont(font);
// headStyle.WrapText = true;
// //headStyle.FillForegroundColor = GetXLColour(workbook, AppConfig.ZhongTaiLightRed);
// //headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, dtSource.Columns.Count - 1 < 0 ? 0 : dtSource.Columns.Count - 1));
// }
//}
//#endregion
rowIndex = -1;
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex + 1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
//font.Color = HSSFColor.WHITE.index;
headStyle.SetFont(font);
headStyle.WrapText = true;
//headStyle.FillForegroundColor = GetXLColour(workbook, AppConfig.ZhongTaiLightRed);
//headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(new Regex(" ").Replace(column.ColumnName, "\n", 1));
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
}
#endregion
rowIndex += 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
if (boolKeyValue != null)
{
string value;
boolKeyValue.TryGetValue(boolV, out value);
newCell.SetCellValue(value);
}
else
{
newCell.SetCellValue(boolV ? "√" : "×");
}
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
rowIndex += 2;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
//sheet.Dispose();
return(ms);
}
}
/// <summary>
/// DataTable 数据添加到Excel模板中.
/// </summary>
/// <param name="path"></param>
/// <param name="dt">输入DataTable</param>
/// <param name="startRow">NPOI开始的行号,从0开始.(对应的Excel列从0开始行).</param>
/// <param name="rowHeight">行高.(Excel中设置的值,方法中已进行x20计算)</param>
/// <param name="styleCell_row">样式单元的行号,默认0行.</param>
/// <param name="styleCell_col">样式单元的列号,默认0列.</param>
/// <returns></returns>
public static string CreateExcelForDataTable(string path, DataTable dt, int startRow = 0, int rowHeight = 0, int styleCell_row = 0, int styleCell_col = 0, Dictionary <string, string> replaceStr = null)
{
try
{
//将文件读到内存，在内存中操作excel
//打开Excel
IWorkbook workbook = OpenExcel(path);
ISheet sheet = workbook.GetSheetAt(0);//.a.CreateSheet("Sheet0");//创建一个名称为Sheet0的表
#region 替换表单中指定字符串
if (replaceStr != null)
{
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
for (int j = row.FirstCellNum; j < sheet.GetRow(i).LastCellNum; j++)
{
string str = row.GetCell(j).StringCellValue;
foreach (string k in replaceStr.Keys)
{
if (str.Contains(k))
{
row.GetCell(j).SetCellValue(str.Replace(k, replaceStr[k]));
replaceStr.Remove(k);
break;
}
}
}
}
}
}
#endregion
//插入行.第"startRow"到第"sheet.LastRowNum"行移动"dt.Rows.Count"行,bool copyRowHeight, bool resetOriginalRowHeight
sheet.ShiftRows(startRow, sheet.LastRowNum, dt.Rows.Count, true, true);
foreach (DataRow dr in dt.Rows)
{
IRow excelRow = sheet.CreateRow(startRow);
//设置行高 ,excel行高度每个像素点是1/20
if (rowHeight > 0)
{
excelRow.Height = (short)(rowHeight * 20);
}
//int curStartCol = startCol;
for (int j = 0; j < dt.Columns.Count; j++)
{
var excelCell = excelRow.CreateCell(j, CellType.String);
excelCell.CellStyle = sheet.GetRow(styleCell_row).Cells[styleCell_col].CellStyle;
var value = dr[j].ToString();
if (File.Exists(value))
{
byte[] bytes = File.ReadAllBytes(value);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2)
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//参数的解析: HSSFClientAnchor（int dx1,int dy1,int dx2,int dy2,int col1,int row1,int col2,int row2)
//dx1: 图片左边相对excel格的位置(x偏移) 范围值为: 0~1023; 即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
//dy1: 图片上方相对excel格的位置(y偏移) 范围值为: 0~256 原理同上。
//dx2: 图片右边相对excel格的位置(x偏移) 范围值为: 0~1023; 原理同上。
//dy2: 图片下方相对excel格的位置(y偏移) 范围值为: 0~256 原理同上。
//col1和row1: 图片左上角的位置，以excel单元格为参考,比喻这两个值为(1, 1)，那么图片左上角的位置就是excel表(1, 1)单元格的右下角的点(A, 1)右下角的点。
//col2和row2: 图片右下角的位置，以excel单元格为参考,比喻这两个值为(2, 2)，那么图片右下角的位置就是excel表(2, 2)单元格的右下角的点(B, 2)右下角的点。
IClientAnchor anchor = null;
if (path.IndexOf(".xlsx") > 0)
{
// 2007版本
anchor = new XSSFClientAnchor(0, 0, 0, 0, j, startRow, j + 1, startRow + 1);
}
else if (path.IndexOf(".xls") > 0)
{
// 2003版本
anchor = new HSSFClientAnchor(0, 0, 0, 0, j, startRow, j + 1, startRow + 1);
}
//把图片插到相应的位置
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
excelCell.SetCellValue(value);
}
}
startRow++;
}
sheet.ForceFormulaRecalculation = true;
//保存Excel
SaveAsExel(workbook, path);
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// 将datatable导出为excel
/// 图片默认显示在excel 第二行最后一列
/// </summary>
/// <param name="table">数据源</param>
/// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
/// <param name="sheetName">工作簿名称</param>
/// <param name="picBytes">导出图片字节流</param>
/// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
/// <returns></returns>
public static MemoryStream ExportToExcel2007(DataTable table, List <Tuple <string, string, int> > excelInfo, string sheetName, byte[] picBytes, List <CellRangeAddress> mergedRegion)
{
MemoryStream ms = new MemoryStream();
try
{
using (table)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
for (int i = 0; i < excelInfo.Count; i++)
{
sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
}
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelInfo.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < excelInfo.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
}
rowIndex++;
}
//合并单元格
if (mergedRegion != null && mergedRegion.Count > 0)
{
foreach (CellRangeAddress cellRangeAddress in mergedRegion)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(cellRangeAddress);
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//将新的样式赋给单元格
var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
cell.CellStyle = style;
}
}
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
var row1 = 2;
var col1 = excelInfo.Count + 1;
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, PictureType.PNG); //添加图片
/* Create the drawing container */
IDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
/* Create an anchor point */
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 0, 0, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
IPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize();
picBytes = null;
}
workbook.Write(ms);
workbook.Close();
}
}
catch (Exception ex)
{
ms = null;
}
return(ms);
}
