/// <summary>
/// 设置单元格值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="value">值</param>
public static void SetValue(this ICell cell, object value)
{
if (cell == null)
{
return;
}
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
if (value.GetType().FullName.Equals("System.Byte[]"))
{
var pictureIndex = cell.Sheet.Workbook.AddPicture((byte[])value, PictureType.PNG);
IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColumnSpan;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan;
IDrawing drawing = cell.Sheet.CreateDrawingPatriarch();
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
return;
}
TypeCode valueTypeCode = Type.GetTypeCode(value.GetType());
switch (valueTypeCode)
{
case TypeCode.String:
cell.SetCellValue(Convert.ToString(value));
break;
case TypeCode.DateTime:
cell.SetCellValue(Convert.ToDateTime(value));
break;
case TypeCode.Boolean:
cell.SetCellValue(Convert.ToBoolean(value));
break;
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Byte:
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
cell.SetCellValue(Convert.ToDouble(value));
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctTwoCellAnchor = this.drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor)anchor;
ctTwoCellAnchor.from = xssfClientAnchor.GetFrom();
ctTwoCellAnchor.to = xssfClientAnchor.GetTo();
ctTwoCellAnchor.AddNewClientData();
xssfClientAnchor.SetTo(ctTwoCellAnchor.to);
xssfClientAnchor.SetFrom(ctTwoCellAnchor.from);
NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs stEditAs;
switch (anchor.AnchorType)
{
case 0:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.twoCell;
break;
case 2:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.oneCell;
break;
case 3:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.absolute;
break;
default:
stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.oneCell;
break;
}
ctTwoCellAnchor.editAs = stEditAs;
ctTwoCellAnchor.editAsSpecified = true;
return(ctTwoCellAnchor);
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
//add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
ICreationHelper helper = wb.GetCreationHelper();
// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();
// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
FileStream sw = File.Create("../../data/image.xlsx");
wb.Write(sw);
sw.Close();
}
////重载
//public ISheet pictureDataToSheet(ISheet sheet, HSSFPicture pictureNPOI, int startRow, int startCol, int endRow, int endCol)
//{
// workbook.AddPicture(pictureNPOI.)
//}
/// <summary>
/// 画矩形，2.0似乎还未实现，xslx格式未实现
/// </summary>
/// <param name="sheet"></param>
/// <param name="content_string"></param>
/// <param name="dx1"></param>
/// <param name="dy1"></param>
/// <param name="dx2"></param>
/// <param name="dy2"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
/// <returns></returns>
public ISheet shapJuxingToSheet(ISheet sheet, string content_string, int dx1, int dy1, int dx2, int dy2, int startRow, int startCol, int endRow, int endCol)
{
if (sheet == null)
{
sheet = this.workbook.CreateSheet();
}
//创建DrawingPatriarch，存放的容器
IDrawing patriarch = sheet.CreateDrawingPatriarch();
//画图形的位置点
IClientAnchor anchor = patriarch.CreateAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);
//将图片文件读入workbook，用索引指向该文件
//int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.TIFF);
//IShape recl;
//根据读入图片和anchor把图片插到相应的位置
//IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
//原始大小显示,重载可指定缩放
//pict.Resize(0.9);
return(sheet);
}
/**
* Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells.
*
* @return a new CT_TwoCellAnchor
*/
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;
ctAnchor.from = (xssfanchor.GetFrom());
ctAnchor.to = (xssfanchor.GetTo());
ctAnchor.AddNewClientData();
xssfanchor.SetTo(ctAnchor.to);
xssfanchor.SetFrom(ctAnchor.from);
ST_EditAs aditAs;
switch (anchor.AnchorType)
{
case (int)AnchorType.DONT_MOVE_AND_RESIZE:
aditAs = ST_EditAs.absolute; break;
case (int)AnchorType.MOVE_AND_RESIZE:
aditAs = ST_EditAs.twoCell; break;
case (int)AnchorType.MOVE_DONT_RESIZE:
aditAs = ST_EditAs.oneCell; break;
default:
aditAs = ST_EditAs.oneCell;
break;
}
ctAnchor.editAs = aditAs;
ctAnchor.editAsSpecified = true;
return(ctAnchor);
}
/// <summary>
/// XSSFSheet清除指定区域的图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="minRow"></param>
/// <param name="maxRow"></param>
/// <param name="minCol"></param>
/// <param name="maxCol"></param>
/// <param name="onlyInternal"></param>
private static void RemovePictures(XSSFSheet sheet, int?minRow, int?maxRow, int?minCol, int?maxCol,
bool onlyInternal)
{
List <POIXMLDocumentPart> documentPartList = sheet.GetRelations();
foreach (POIXMLDocumentPart documentPart in documentPartList)
{
if (documentPart is XSSFDrawing)
{
var drawing = (XSSFDrawing)documentPart;
List <XSSFShape> shapeList = drawing.GetShapes();
for (int i = 0; i < shapeList.Count; i++)
{
XSSFShape shape = shapeList[i];
if (shape is XSSFPicture)
{
var picture = (XSSFPicture)shape;
IClientAnchor anchor = picture.GetPreferredSize();
if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2,
anchor.Col1, anchor.Col2, onlyInternal))
{
throw new NotImplementedException("XSSFSheet未实现ClearPictures()方法！");
}
}
}
}
}
}
public void TestQuickGuide()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(3).CreateCell(5);
cell.SetCellValue("F4");
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = factory.CreateClientAnchor();
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = (str);
comment.Author = ("Apache POI");
//assign the comment to the cell
cell.CellComment = (comment);
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
cell = sheet.GetRow(3).GetCell(5);
comment = cell.CellComment;
Assert.IsNotNull(comment);
Assert.AreEqual("Hello, World!", comment.String.String);
Assert.AreEqual("Apache POI", comment.Author);
Assert.AreEqual(3, comment.Row);
Assert.AreEqual(5, comment.Column);
}
private void AddChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor)
{
var chart = drawing.CreateChart(anchor);
var legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.Bottom;
var data = chart.ChartDataFactory.CreateLineChartData <string, double>();
var bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
var leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
var row = sheet.CreateRow(0);
for (int i = 0; i < _errorList.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(_errorList[i]);
}
var xAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, _errorList.Count - 1));
FillChartDataCells(sheet, 1, _dichotomyIterations, xAxis, data);
FillChartDataCells(sheet, 2, _goldenRatioIterations, xAxis, data);
FillChartDataCells(sheet, 3, _fibonacciIterations, xAxis, data);
FillChartDataCells(sheet, 4, _parabolaIterations, xAxis, data);
FillChartDataCells(sheet, 5, _brentIterations, xAxis, data);
chart.Plot(data, bottomAxis, leftAxis);
}
private static void PostilAdd(Tk5ListMetaData metaInfos, ImportError importError, IWorkbook workBook, ISheet sheet)
{
IDrawing part = sheet.CreateDrawingPatriarch();
Dictionary <string, int> indexOfName = new Dictionary <string, int>();
int i = 0;
foreach (var info in metaInfos.Table.TableList)
{
indexOfName.Add(info.DisplayName, i);
i++;
}
foreach (var err in importError)
{
IRow row = sheet.GetRow(err.IndexOfRow);
IComment comment = null;
ICell cell = row.GetCell(indexOfName[err.ColumnName]);
ICreationHelper factory = workBook.GetCreationHelper();
IClientAnchor anchor = null;
anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex + 2;
anchor.Col2 = cell.ColumnIndex + 4;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = part.CreateCellComment(anchor);
comment.Author = "mitu";
comment.String = new HSSFRichTextString(err.ErrorMsg);
cell.CellComment = comment;
}
}
public void BaseTestResize(IPicture input, IPicture Compare, double scaleX, double scaleY)
{
input.Resize(scaleX, scaleY);
IClientAnchor inpCA = input.ClientAnchor;
IClientAnchor cmpCA = Compare.ClientAnchor;
Size inpDim = ImageUtils.GetDimensionFromAnchor(input);
Size cmpDim = ImageUtils.GetDimensionFromAnchor(Compare);
double emuPX = Units.EMU_PER_PIXEL;
Assert.AreEqual(inpDim.Height, cmpDim.Height, emuPX * 6, "the image height differs");
Assert.AreEqual(inpDim.Width, cmpDim.Width, emuPX * 6, "the image width differs");
Assert.AreEqual(inpCA.Col1, cmpCA.Col1, "the starting column differs");
Assert.AreEqual(inpCA.Dx1, cmpCA.Dx1, 1, "the column x-offset differs");
Assert.AreEqual(inpCA.Dy1, cmpCA.Dy1, 1, "the column y-offset differs");
Assert.AreEqual(inpCA.Col2, cmpCA.Col2, "the ending columns differs");
// can't compare row heights because of variable test heights
input.Resize();
inpDim = ImageUtils.GetDimensionFromAnchor(input);
Size imgDim = input.GetImageDimension();
Assert.AreEqual(imgDim.Height, inpDim.Height / emuPX, 1, "the image height differs");
Assert.AreEqual(imgDim.Width, inpDim.Width / emuPX, 1, "the image width differs");
}
private static void CreateChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, string serieTitle, int startDataRow, int endDataRow, int columnIndex)
{
XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
IBarChartData <string, double> barChartData = chart.ChartDataFactory.CreateBarChartData <string, double>();
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.Bottom;
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> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0));
IChartDataSource <double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex));
var serie = barChartData.AddSeries(categoryAxis, valueAxis);
serie.SetTitle(serieTitle);
chart.Plot(barChartData, bottomAxis, leftAxis);
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("linechart");
// 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);
cell.SetCellValue(colIndex * (rowIndex + 1));
}
}
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
CreateChart(drawing, sheet, anchor1, "title1", "title2");
IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35);
CreateChart(drawing, sheet, anchor2, "s1", "s2");
using (FileStream fs = File.Create("test.xlsx"))
{
wb.Write(fs);
}
}
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2)
{
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>();
// Use a category axis for the bottom axis.
IChartAxis bottomAxis = chart.GetChartAxisFactory().CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Left);
leftAxis.SetCrosses(AxisCrosses.AutoZero);
IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle(serie1);
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle(serie2);
chart.Plot(data, bottomAxis, leftAxis);
}
/**
* Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells.
*
* @return a new CT_TwoCellAnchor
*/
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;
ctAnchor.from = (xssfanchor.From);
ctAnchor.to = (xssfanchor.To);
ctAnchor.AddNewClientData();
xssfanchor.To = ctAnchor.to;
xssfanchor.From = ctAnchor.from;
ST_EditAs aditAs;
switch (anchor.AnchorType)
{
case AnchorType.DontMoveAndResize:
aditAs = ST_EditAs.absolute; break;
case AnchorType.MoveAndResize:
aditAs = ST_EditAs.twoCell; break;
case AnchorType.MoveDontResize:
aditAs = ST_EditAs.oneCell; break;
default:
aditAs = ST_EditAs.oneCell;
break;
}
ctAnchor.editAs = aditAs;
ctAnchor.editAsSpecified = true;
return(ctAnchor);
}
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2, bool enableMajorGridline = false)
{
XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
chart.SetTitle("Test 1");
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>();
// Use a category axis for the bottom axis.
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle(serie1);
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle(serie2);
chart.Plot(data, bottomAxis, leftAxis);
//add major gridline, available since NPOI 2.5.5
var plotArea = chart.GetCTChart().plotArea;
plotArea.catAx[0].AddNewMajorGridlines();
plotArea.valAx[0].AddNewMajorGridlines();
}
public override async Task <IWorkbook> DoExport(int projectId)
{
var workbook = await base.DoExport(projectId);
var sheet = workbook.GetSheetAt(0);
//获取最后一条报工记录
var lastReport = await ProcessTaskReportManager.GetAll().Where(o => o.ProcessTask.Part.ProjectId == projectId).LastOrDefaultAsync();
if (lastReport != null && lastReport.Files.Count > 0)
{
var maxRowNum = sheet.LastRowNum;
sheet.CreateRow(maxRowNum + 10);
var file = lastReport.Files[0];
var patriarch = sheet.CreateDrawingPatriarch();
sheet.AddMergedRegion(new CellRangeAddress(maxRowNum + 1, maxRowNum + 10, 0, 3));
if (!string.IsNullOrEmpty(file.FilePath))
{
try
{
byte[] bytes = System.IO.File.ReadAllBytes(Common.PathHelper.VirtualPathToAbsolutePath(file.FilePath));
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
IClientAnchor anchor = patriarch.CreateAnchor(70, 10, 0, 0, 0, maxRowNum + 1, 3, maxRowNum + 10);
//把图片插到相应的位置
patriarch.CreatePicture(anchor, pictureIdx);
}
catch (Exception ex)
{
}
}
}
return(workbook);
}
public void GetCellComment()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
ICreationHelper factory = wb.GetCreationHelper();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(1);
// cell does not have a comment
Assert.IsNull(cell.CellComment);
// add a cell comment
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 1;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
IDrawing drawing = sheet.CreateDrawingPatriarch();
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = str;
comment.Author = "Apache POI";
cell.CellComment = comment;
// ideally assertSame, but XSSFCell creates a new XSSFCellComment wrapping the same bean for every call to getCellComment.
Assert.AreEqual(comment, cell.CellComment);
wb.Close();
}
public void TestFormulaCache()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = new SheetBuilder(wb, plotData).Build();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
IChartAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
IScatterChartData scatterChartData =
chart.ChartDataFactory.CreateScatterChartData();
DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A1:E1"));
DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A2:E2"));
IScatterChartSerie serie = scatterChartData.AddSerie(xMarker, yMarker);
chart.Plot(scatterChartData, bottomAxis, leftAxis);
XSSFScatterChartData.Serie xssfScatterSerie =
(XSSFScatterChartData.Serie)serie;
XSSFNumberCache yCache = xssfScatterSerie.LastCalculatedYCache;
Assert.AreEqual(5, yCache.PointCount);
Assert.AreEqual(4.0, yCache.GetValueAt(3), 0.00001);
Assert.AreEqual(16.0, yCache.GetValueAt(5), 0.00001);
}
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2)
{
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData<double, double> data = chart.ChartDataFactory.CreateLineChartData<double, double>();
// Use a category axis for the bottom axis.
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource<double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle(serie1);
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle(serie2);
chart.Plot(data, bottomAxis, leftAxis);
}
/// <summary>
/// 删除指定区域的图片
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="firstRow">起始行索引</param>
/// <param name="lastRow">结束行索引</param>
/// <param name="firstColumn">起始列索引</param>
/// <param name="lastColumn">结束列索引</param>
/// <param name="onlyInternal">是否内部区域</param>
private static void RemovePictures(XSSFSheet sheet, int?firstRow, int?lastRow,
int?firstColumn, int?lastColumn, bool onlyInternal)
{
throw new NotImplementedException($"XSSFSheet 未实现RemovePictures()方法！");
var documentPartList = sheet.GetRelations();
foreach (var documentPart in documentPartList)
{
if (documentPart is XSSFDrawing drawing)
{
List <XSSFShape> shapes = drawing.GetShapes();
foreach (var shape in shapes)
{
if (shape is XSSFPicture picture)
{
IClientAnchor anchor = picture.GetPreferredSize();
if (IsInternalOrIntersect(firstRow, lastRow, firstColumn, lastColumn, anchor.Row1, anchor.Row2,
anchor.Col1, anchor.Row2, onlyInternal))
{
}
}
}
}
}
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
//create comments and vmlDrawing parts if they don't exist
CommentsTable comments = sheet.GetCommentsTable(true);
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
// convert offsets from emus to pixels since we get a DrawingML-anchor
// but create a VML Drawing
int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL;
int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL;
int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL;
int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL;
String position =
ca.Col1 + ", " + dx1Pixels + ", " +
ca.Row1 + ", " + dy1Pixels + ", " +
ca.Col2 + ", " + dx2Pixels + ", " +
ca.Row2 + ", " + dy2Pixels;
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString();
if (comments.FindCellComment(ref1) != null)
{
throw new ArgumentException("Multiple cell comments in one cell are not allowed, cell: " + ref1);
}
return(new XSSFComment(comments, comments.NewComment(ref1), vmlShape));
}
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet)
{
var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2);
sheet.AddMergedRegion(merge);
var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var caminho = $"{diretorioAtual}/Recursos/logoVLI.png";
byte[] data = ArquivosUtil.RetornarArquivo(caminho);
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 1;
anchor.Row1 = 1;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/
return(sheet);
}
public void TestOneSeriePlot()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = new SheetBuilder(wb, plotData).Build();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
IChartAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
IScatterChartData <string, double> scatterChartData =
chart.ChartDataFactory.CreateScatterChartData <string, double>();
IChartDataSource <String> xs = DataSources.FromStringCellRange(sheet, CellRangeAddress.ValueOf("A1:J1"));
IChartDataSource <double> ys = DataSources.FromNumericCellRange(sheet, CellRangeAddress.ValueOf("A2:J2"));
IScatterChartSeries <string, double> series = scatterChartData.AddSeries(xs, ys);
Assert.IsNotNull(series);
Assert.AreEqual(1, scatterChartData.GetSeries().Count);
Assert.IsTrue(scatterChartData.GetSeries().Contains(series));
chart.Plot(scatterChartData, bottomAxis, leftAxis);
}
/// <summary>
/// 在单元格中插入图片
/// </summary>
private static void InsertImage(ICell cell, byte[] picBytes, PictureType pt)
{
var row1 = cell.RowIndex;
var col1 = cell.ColumnIndex;
var workbook = cell.Sheet.Workbook;
var sheet = cell.Sheet;
/* Calcualte the image size */
MemoryStream ms = new MemoryStream(picBytes);
Image Img = Bitmap.FromStream(ms, true);
double ImageOriginalWidth = Img.Width;
double ImageOriginalHeight = Img.Height;
double scale = cell.Sheet.GetColumnWidth(cell.ColumnIndex) / 36.5 / ImageOriginalWidth;
cell.Row.Height = (short)(ImageOriginalHeight * scale * 15);
//cell.Sheet.SetColumnWidth(cell.ColumnIndex, (int)(ImageOriginalWidth * scale * 36.5));
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, pt); //添加图片
/* Create the drawing container */
IDrawing drawing = sheet.CreateDrawingPatriarch();
/* Create an anchor point */
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
IPicture picture = drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize(1);
picBytes = null;
}
/// <summary>
/// 设置批注
/// </summary>
/// <param name="cell"></param>
/// <param name="suffix"></param>
/// <param name="comment"></param>
/// <param name="col1"></param>
/// <param name="row1"></param>
/// <param name="col2"></param>
/// <param name="row2"></param>
public static void SetCellComment(this ICell cell, CommentEntity entitiy)
{
ISheet sheet = cell.Sheet;
IClientAnchor clientAnchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
clientAnchor.AnchorType = AnchorType.MoveDontResize.GetType().ToInt();
clientAnchor.Dx1 = entitiy.Dx1;
clientAnchor.Dy1 = entitiy.Dy1;
clientAnchor.Dx2 = entitiy.Dx2;
clientAnchor.Dy2 = entitiy.Dy2;
clientAnchor.Col1 = cell.ColumnIndex;
clientAnchor.Row1 = cell.RowIndex;
clientAnchor.Col2 = cell.ColumnIndex + entitiy.Width;
clientAnchor.Row2 = cell.RowIndex + entitiy.Height;
IDrawing draw = sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(clientAnchor);
comment.Visible = false;
if (sheet.Workbook is HSSFWorkbook)
{
comment.String = new HSSFRichTextString(entitiy.Text);
}
else
{
comment.String = new XSSFRichTextString(entitiy.Text);
}
cell.CellComment = comment;
}
/*泛类型行不通
* 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.)
* }
*/
/// <summary>
/// Excel sheet中插入图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="pictureNPOI"></param>
/// <param name="dx1"></param>
/// <param name="dy1"></param>
/// <param name="dx2"></param>
/// <param name="dy2"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
/// <returns></returns>
//重载
public void pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int dx1, int dy1, int dx2, int dy2, 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(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);
IClientAnchor anchor = patriarch.CreateAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);
//将图片文件读入workbook，用索引指向该文件
int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG);
//根据读入图片和anchor把图片插到相应的位置
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
//原始大小显示,重载可指定缩放
//pict.Resize(0.9);
//return sheet;
}
private IWorkbook OpenExcel(String filePath, params Byte[][] imageData)
{
IWorkbook workBook = null;
if (File.Exists(filePath))
{
using (FileStream stream = new FileStream(filePath, FileMode.Open,
FileAccess.ReadWrite, FileShare.ReadWrite, 8096,
FileOptions.WriteThrough))
{
workBook = GenerationWorkBook(filePath, stream);
Int32 index = workBook.AddPicture(imageData[0], PictureType.PNG);
ISheet sheet = workBook.GetSheetAt(0);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
Int32 lastRowNum = sheet.LastRowNum;
IClientAnchor anchor = GenerationClientAnchor(filePath, 0, 0, 0, 0, 1, lastRowNum + 2, 8, lastRowNum + 25);
IPicture pic = patriarch.CreatePicture(anchor, index);
File.Delete(filePath);
using (FileStream newStream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
{
workBook.Write(newStream);
}
}
}
return(workBook);
}
private IComment insertComment(IDrawing Drawing, ICell cell, String message)
{
ICreationHelper factory = cell.Sheet.Workbook.GetCreationHelper();
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (/*setter*/ cell.ColumnIndex);
anchor.Col2 = (/*setter*/ cell.ColumnIndex + 1);
anchor.Row1 = (/*setter*/ cell.RowIndex);
anchor.Row2 = (/*setter*/ cell.RowIndex + 1);
anchor.Dx1 = (/*setter*/ 100);
anchor.Dx2 = (/*setter*/ 100);
anchor.Dy1 = (/*setter*/ 100);
anchor.Dy2 = (/*setter*/ 100);
IComment comment = Drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString(message);
comment.String = (/*setter*/ str);
comment.Author = (/*setter*/ "fanfy");
cell.CellComment = (/*setter*/ comment);
return(comment);
}
/// <summary>
/// Creates a polygon
/// </summary>
/// <param name="anchor">the client anchor describes how this Group is attached
/// to the sheet.</param>
/// <returns>the newly Created shape.</returns>
public HSSFPolygon CreatePolygon(IClientAnchor anchor)
{
HSSFPolygon shape = new HSSFPolygon(null, (HSSFAnchor)anchor);
shape.Anchor = (HSSFAnchor)anchor;
AddShape(shape);
return(shape);
}
/// <summary>
/// Constructs a textbox Under the patriarch.
/// </summary>
/// <param name="anchor">the client anchor describes how this Group is attached
/// to the sheet.</param>
/// <returns>the newly Created textbox.</returns>
public HSSFSimpleShape CreateTextbox(IClientAnchor anchor)
{
HSSFTextbox shape = new HSSFTextbox(null, (HSSFAnchor)anchor);
shape.Anchor = (HSSFAnchor)anchor;
AddShape(shape);
return(shape);
}
/// <summary>
/// 创建一个图表实例
/// </summary>
/// <param name="excelChart"></param>
/// <param name="sheet"></param>
private void CreateChart(NPOIExcelChart excelChart, ISheet sheet, int startRow, int endRow)
{
if (_excelType != NPOIExcelType.XLS)
{
throw new NotImplementedException("只支持.xls文件作图");
}
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, startRow, excelChart.Axis.Count, endRow);
XSSFChart chart = drawing.CreateChart(anchor) as XSSFChart;
chart.Title.String = excelChart.Title;
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
//坐标轴
var axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
axis.IsVisible = true;
//值轴
var data = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
data.IsVisible = true;
data.Crosses = (AxisCrosses.AutoZero);
IChartDataSource <string> xs = DataSources.FromArray <string>(excelChart.Axis.ToArray());
switch (excelChart.ExcelChartType)
{
case NPOIExcelChartType.Bar:
var chartBarData = chart.ChartDataFactory.CreateBarChartData <string, double>();
foreach (var item in excelChart.Data)
{
var curData = DataSources.FromArray <double>(item.Value.ToArray());
var curSeries = chartBarData.AddSeries(xs, curData);
curSeries.SetTitle(item.Key);
}
chart.Plot(chartBarData, axis, data);
return;
case NPOIExcelChartType.Line:
var chartLineData = chart.ChartDataFactory.CreateLineChartData <string, double>();
foreach (var item in excelChart.Data)
{
var curData = DataSources.FromArray <double>(item.Value.ToArray());
var curSeries = chartLineData.AddSeries(xs, curData);
curSeries.SetTitle(item.Key);
}
chart.Plot(chartLineData, axis, data);
break;
default:
break;
}
}
public void BaseTestResize(IClientAnchor referenceAnchor)
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh1 = wb.CreateSheet();
IDrawing p1 = sh1.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
byte[] pictureData = _testDataProvider.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData,PictureType.PNG);
IPicture picture = p1.CreatePicture(factory.CreateClientAnchor(), idx1);
picture.Resize();
IClientAnchor anchor1 = picture.GetPreferredSize();
//assert against what would BiffViewer print if we insert the image in xls and dump the file
Assert.AreEqual(referenceAnchor.Col1, anchor1.Col1);
Assert.AreEqual(referenceAnchor.Row1, anchor1.Row1);
Assert.AreEqual(referenceAnchor.Col2, anchor1.Col2);
Assert.AreEqual(referenceAnchor.Row2, anchor1.Row2);
Assert.AreEqual(referenceAnchor.Dx1, anchor1.Dx1);
Assert.AreEqual(referenceAnchor.Dy1, anchor1.Dy1);
Assert.AreEqual(referenceAnchor.Dx2, anchor1.Dx2);
Assert.AreEqual(referenceAnchor.Dy2, anchor1.Dy2);
}
public IPicture CreatePicture(IClientAnchor anchor, int pictureIndex)
{
return CreatePicture((XSSFClientAnchor)anchor, pictureIndex);
}
/**
* Constructs a textbox under the Drawing.
*
* @param anchor the client anchor describes how this group is attached
* to the sheet.
* @return the newly Created textbox.
*/
public ITextbox CreateTextbox(IClientAnchor anchor)
{
long shapeId = newShapeId();
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
CT_Shape ctShape = ctAnchor.AddNewSp();
ctShape.Set(XSSFSimpleShape.Prototype());
ctShape.nvSpPr.cNvPr.id=(uint)shapeId;
XSSFTextBox shape = new XSSFTextBox(this, ctShape);
shape.anchor = (XSSFClientAnchor)anchor;
return shape;
}
/// <summary>
/// Creates a polygon
/// </summary>
/// <param name="anchor">the client anchor describes how this Group is attached
/// to the sheet.</param>
/// <returns>the newly Created shape.</returns>
public HSSFPolygon CreatePolygon(IClientAnchor anchor)
{
HSSFPolygon shape = new HSSFPolygon(null, (HSSFAnchor)anchor);
shape.Anchor = (HSSFAnchor)anchor;
AddShape(shape);
return shape;
}
/// <summary>
/// Constructs a textbox Under the patriarch.
/// </summary>
/// <param name="anchor">the client anchor describes how this Group is attached
/// to the sheet.</param>
/// <returns>the newly Created textbox.</returns>
public HSSFSimpleShape CreateTextbox(IClientAnchor anchor)
{
HSSFTextbox shape = new HSSFTextbox(null, (HSSFAnchor)anchor);
shape.Anchor = (HSSFAnchor)anchor;
AddShape(shape);
return shape;
}
/// <summary>
/// Constructs a cell comment.
/// </summary>
/// <param name="anchor">the client anchor describes how this comment is attached
/// to the sheet.</param>
/// <returns>the newly created comment.</returns>
public IComment CreateCellComment(IClientAnchor anchor)
{
return CreateComment((HSSFAnchor)anchor);
}
public IChart CreateChart(IClientAnchor anchor)
{
throw new RuntimeException("NotImplemented");
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
//create comments and vmlDrawing parts if they don't exist
CommentsTable comments = sheet.GetCommentsTable(true);
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
String position =
ca.Col1 + ", 0, " + ca.Row1 + ", 0, " +
ca.Col2 + ", 0, " + ca.Row2 + ", 0";
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
XSSFComment shape = new XSSFComment(comments, comments.CreateComment(), vmlShape);
shape.Column = (ca.Col1);
shape.Row = (ca.Row1);
return shape;
}
/**
* Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells.
*
* @return a new CT_TwoCellAnchor
*/
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;
ctAnchor.from = (xssfanchor.From);
ctAnchor.to = (xssfanchor.To);
ctAnchor.AddNewClientData();
xssfanchor.To = ctAnchor.to;
xssfanchor.From = ctAnchor.from;
ST_EditAs aditAs;
switch (anchor.AnchorType)
{
case AnchorType.DontMoveAndResize:
aditAs = ST_EditAs.absolute; break;
case AnchorType.MoveAndResize:
aditAs = ST_EditAs.twoCell; break;
case AnchorType.MoveDontResize:
aditAs = ST_EditAs.oneCell; break;
default:
aditAs = ST_EditAs.oneCell;
break;
}
ctAnchor.editAs = aditAs;
ctAnchor.editAsSpecified = true;
return ctAnchor;
}
/// <summary>
/// Creates a chart.
/// </summary>
/// <param name="anchor">the client anchor describes how this chart is attached to</param>
/// <returns>the newly created chart</returns>
public IChart CreateChart(IClientAnchor anchor)
{
int chartNumber = GetPackagePart().Package.
GetPartsByContentType(XSSFRelation.CHART.ContentType).Count + 1;
XSSFChart chart = (XSSFChart)CreateRelationship(
XSSFRelation.CHART, XSSFFactory.GetInstance(), chartNumber);
String chartRelId = chart.GetPackageRelationship().Id;
XSSFGraphicFrame frame = CreateGraphicFrame((XSSFClientAnchor)anchor);
frame.SetChart(chart, chartRelId);
return chart;
}
/**
* Returns all charts in this Drawing.
*/
//public List<XSSFChart> GetCharts()
//{
// List<XSSFChart> charts = new List<XSSFChart>();
// foreach (POIXMLDocumentPart part in GetRelations())
// {
// if (part is XSSFChart)
// {
// charts.Add((XSSFChart)part);
// }
// }
// return charts;
//}
/**
* Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells.
*
* @return a new CT_TwoCellAnchor
*/
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor)
{
CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor();
XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;
ctAnchor.from = (xssfanchor.GetFrom());
ctAnchor.to = (xssfanchor.GetTo());
ctAnchor.AddNewClientData();
xssfanchor.SetTo(ctAnchor.to);
xssfanchor.SetFrom(ctAnchor.from);
ST_EditAs aditAs;
switch (anchor.AnchorType)
{
case (int)AnchorType.DONT_MOVE_AND_RESIZE:
aditAs = ST_EditAs.absolute; break;
case (int)AnchorType.MOVE_AND_RESIZE:
aditAs = ST_EditAs.twoCell; break;
case (int)AnchorType.MOVE_DONT_RESIZE:
aditAs = ST_EditAs.oneCell; break;
default:
aditAs = ST_EditAs.oneCell;
break;
}
ctAnchor.editAs = aditAs;
ctAnchor.editAsSpecified = true;
return ctAnchor;
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
//create comments and vmlDrawing parts if they don't exist
CommentsTable comments = sheet.GetCommentsTable(true);
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
// convert offsets from emus to pixels since we get a DrawingML-anchor
// but create a VML Drawing
int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL;
int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL;
int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL;
int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL;
String position =
ca.Col1 + ", " + dx1Pixels + ", " +
ca.Row1 + ", " + dy1Pixels + ", " +
ca.Col2 + ", " + dx2Pixels + ", " +
ca.Row2 + ", " + dy2Pixels;
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString();
if (comments.FindCellComment(ref1) != null)
{
throw new ArgumentException("Multiple cell comments in one cell are not allowed, cell: " + ref1);
}
return new XSSFComment(comments, comments.NewComment(ref1), vmlShape);
}
/**
* Creates a chart.
* @param anchor the client anchor describes how this chart is attached to
* the sheet.
* @return the newly Created chart
* @see NPOI.xssf.usermodel.XSSFDrawing#CreateChart(ClientAnchor)
*/
public IChart CreateChart(IClientAnchor anchor)
{
//int chartNumber = GetPackagePart().Package.
// GetPartsByContentType(XSSFRelation.CHART.ContentType).Count + 1;
throw new NotImplementedException();
// XSSFChart chart = (XSSFChart)CreateRelationship(
// XSSFRelation.CHART, XSSFFactory.GetInstance(), chartNumber);
// String chartRelId = chart.GetPackageRelationship().GetId();
// XSSFGraphicFrame frame = CreateGraphicFrame(anchor);
// frame.SetChart(chart, chartRelId);
// return chart;
}
// Set the comment on a sheet
//
private static void setComment(ISheet sheet, ICell cell, IDrawing drawing, String commentText, ICreationHelper helper, IClientAnchor anchor)
{
//System.out.println("Setting col: " + cell.getColumnIndex() + " and row " + cell.getRowIndex());
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex);
anchor.Row1 = (cell.RowIndex);
anchor.Row2 = (cell.RowIndex);
// get comment, or create if it does not exist
// NOTE - only occurs if getCellComment is called first
IComment comment = cell.CellComment;
//Comment comment = null;
if (comment == null)
{
comment = drawing.CreateCellComment(anchor);
}
comment.Author = ("Test");
// attach the comment to the cell
comment.String = (helper.CreateRichTextString(commentText));
cell.CellComment = (comment);
}
public XSSFCheckBox CreateCheckbox(IClientAnchor anchor)
{
// create drawing
anchor.AnchorType = AnchorType.MoveDontResize;
long shapeId = newShapeId();
CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor);
ctAnchor.isAlternateContentChoice = true;
CT_Shape ctShape = ctAnchor.AddNewSp();
ctShape.Set(XSSFCheckboxShape.GetPrototype());
ctShape.nvSpPr.cNvPr.id = (uint)shapeId;
ctShape.nvSpPr.cNvPr.extLst = new CT_OfficeArtExtensionList
{
ext = new List<CT_OfficeArtExtension>
{
new CT_OfficeArtExtension{
uri = "{63B3BB69-23CF-44E3-9099-C40C66FF867C}",
Any = "<a14:compatExt spid=\"_x0000_s" + shapeId + "\"/>",
},
},
};
ctShape.nvSpPr.cNvPr.name = "Checkbox " + shapeId;
// create vmlDrawing
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCheckboxShape();
if (ca.IsSet())
{
// convert offsets from emus to pixels since we get a DrawingML-anchor
// but create a VML Drawing
int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL;
int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL;
int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL;
int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL;
String position =
ca.Col1 + ", " + dx1Pixels + ", " +
ca.Row1 + ", " + dy1Pixels + ", " +
ca.Col2 + ", " + dx2Pixels + ", " +
ca.Row2 + ", " + dy2Pixels;
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString();
XSSFCtrlProp ctrlProp = sheet.AddCtrlProp();
XSSFCheckBox shape = new XSSFCheckBox(this, ctShape, vmlShape, ctrlProp);
shape.anchor = (XSSFClientAnchor)anchor;
shape.SetText("Checkbox");
// create sheet
NPOI.OpenXmlFormats.Spreadsheet.CT_Checkbox checkbox = sheet.AddNewCheckbox();
checkbox.name = "Check Box " + shapeId;
checkbox.shapeId = shapeId.ToString();
String relId = ctrlProp.GetPackageRelationship().Id;
checkbox.ctrlPropRelId = relId;
checkbox.anchor = new OpenXmlFormats.Spreadsheet.CT_Anchor
{
from = new CT_Marker
{
col = anchor.Col1,
colOff = 100000,
row = anchor.Row1,
rowOff = 300000,
},
to = new CT_Marker
{
col = anchor.Col1,
colOff = 130000,
row = anchor.Row1,
rowOff = 500000,
}
};
return shape;
}
