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);
}
}
protected override void SetValue(ICell cell, object source)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题
}
public void Render(ICell cell)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
PicArea != null?
draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG));
}
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);
}
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);
}
////重载
//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);
}
/*泛类型行不通
* 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;
}
/// <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="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;
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("linechart");
int NUM_OF_ROWS = 3;
int NUM_OF_COLUMNS = 10;
// 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 anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData <double, double> data = chart.GetChartDataFactory().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.AddSerie(xs, ys1);
s1.SetTitle("title1");
var s2 = data.AddSerie(xs, ys2);
s2.SetTitle("title2");
chart.Plot(data, bottomAxis, leftAxis);
using (FileStream fs = File.Create("test.xlsx"))
{
wb.Write(fs);
}
}
public TestXSSFChartAxis()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Bottom);
}
private IManualLayout GetEmptyLayout()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
return(legend.GetManualLayout());
}
public void GenaretionChart()
{
FileStream RfileStream = new FileStream("D:\\test.xlsx", FileMode.Open, FileAccess.Read);
//建立讀取資料的FileStream
XSSFWorkbook wb = new XSSFWorkbook(RfileStream);
//讀取檔案內的Workbook物件
ISheet Wsheet = wb.GetSheetAt(1);
//選擇圖表存放的sheet
ISheet Rsheet = wb.GetSheetAt(0);
//選擇資料來源的sheet
IDrawing drawing = Wsheet.CreateDrawingPatriarch();
//sheet產生drawing物件
IClientAnchor clientAnchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 0, 5, 10);
//設定圖表位置
IChart chart = drawing.CreateChart(clientAnchor);
//產生chart物件
IChartLegend legend = chart.GetOrCreateLegend();
//還沒研究出這行在做甚麼
legend.Position = LegendPosition.TopRight;
ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>();
//產生存放資料的物件(資料型態為double)
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
//設定X軸
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
//設定Y軸
bottomAxis.Crosses = AxisCrosses.AutoZero;
//設定X軸數值開始為0
leftAxis.Crosses = AxisCrosses.AutoZero;
//設定Y軸數值開始為0
IChartDataSource <double> xs = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 0, 0));
//取得要讀取sheet的資料位置(CellRangeAddress(first_row,end_row, first_column, end_column))
//x軸資料
IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 1, 1));
//第一條y軸資料
IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 2, 2));
//第二條y軸資料
data.AddSeries(xs, ys1);
data.AddSeries(xs, ys2);
//加入到data
chart.Plot(data, bottomAxis, leftAxis);
//加入到chart
FileStream WfileStream = new FileStream("D:\\test.xlsx", FileMode.Create, FileAccess.Write);
//建立寫入資料的FileStream
wb.Write(WfileStream);
//將workbook寫入資料
RfileStream.Close();
//關閉FileStream
WfileStream.Close();
//關閉FileStream
}
public void TestLegendPositionAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
Assert.AreEqual(LegendPosition.TopRight, legend.Position);
}
public void Main()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet 1");
int NUM_OF_ROWS = 3;
int NUM_OF_COLUMNS = 10;
// 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 anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = (LegendPosition.TopRight);
IScatterChartData <double, double> data = chart.ChartDataFactory.CreateScatterChartData <double, double>();
IValueAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(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));
data.AddSeries(xs, ys1);
data.AddSeries(xs, ys2);
chart.Plot(data, bottomAxis, leftAxis);
// Write the output to a file
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
public void Test_setOverlay_defaultChartLegend_expectOverlayInitialValueSetToFalse()
{
// Arrange
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
// Act
// Assert
Assert.IsFalse(legend.IsOverlay);
}
private IWorkbook CreateWorkbookWithChart()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("linechart");
int NUM_OF_ROWS = 3;
int NUM_OF_COLUMNS = 10;
// 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 anchor = Drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
IChart chart = Drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = (/*setter*/ 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 = (/*setter*/ 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));
data.AddSeries(xs, ys1);
data.AddSeries(xs, ys2);
chart.Plot(data, bottomAxis, leftAxis);
return(wb);
}
/// <summary>
/// 添加批注
/// </summary>
/// <param name="exCell">单元格</param>
/// <param name="commentAuthor">批注作者</param>
/// <param name="commentContent">批注内容</param>
public static void AddComment(ICell exCell, string commentAuthor, string commentContent)
{
IWorkbook book = exCell.Sheet.Workbook;
//IDrawing draw = exCell.Sheet.DrawingPatriarch ?? exCell.Sheet.CreateDrawingPatriarch();
IDrawing draw = exCell.Sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(draw.CreateAnchor(0, 0, 255, 255, exCell.ColumnIndex + 1, exCell.RowIndex + 1,
exCell.ColumnIndex + 3, exCell.RowIndex + 4));
comment.Author = commentAuthor;
comment.String = book.GetCreationHelper().CreateRichTextString(commentContent);
//用于解决XSSF批注不显示的问题
comment.String.ApplyFont(book.CreateFont());
//comment.Column = exCell.ColumnIndex;
//comment.Row = exCell.RowIndex;
exCell.CellComment = comment;
}
public void TestAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IValueAxis axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM);
axis.SetCrossBetween(AxisCrossBetween.MIDPOINT_CATEGORY);
Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MIDPOINT_CATEGORY);
axis.SetCrosses(AxisCrosses.AUTO_ZERO);
Assert.AreEqual(axis.GetCrosses(), AxisCrosses.AUTO_ZERO);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
public void TestAccessMethods()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IValueAxis axis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
axis.SetCrossBetween(AxisCrossBetween.MidpointCategory);
Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MidpointCategory);
axis.Crosses = (AxisCrosses.AutoZero);
Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero);
Assert.AreEqual(chart.GetAxis().Count, 1);
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="inputSheet"></param>
/// <param name="lstInputRequest"></param>
/// <returns></returns>
public static bool TryInsertPictures(ISheet inputSheet, List <PictureInsertRequest> lstInputRequest)
{
try
{
//获取/创建
IDrawing patriarch = inputSheet.DrawingPatriarch;
if (null == patriarch)
{
patriarch = inputSheet.CreateDrawingPatriarch();
}
foreach (var oneRequest in lstInputRequest)
{
//第一步：将图片读入二进制数组
MemoryStream mStream = new MemoryStream();
oneRequest.UseImage.Save(mStream, System.Drawing.Imaging.ImageFormat.Png);
byte[] bytePic = mStream.GetBuffer();
//第二步：图片加载进workbook
int pictureIdx = inputSheet.Workbook.AddPicture(bytePic, PictureType.PNG);
//第三步：设置锚点
IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, oneRequest.StartColumn, oneRequest.StartRow, oneRequest.EndColumn, oneRequest.EndRow);
//第四步：创建图片
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public static void drawWaterRemarkPath(HSSFWorkbook wb, string waterRemarkPath)
{
int startXCol = 0; // 水印起始列
int startYRow = 2; // 水印起始行
int betweenXCol = 1; // 水印横向之间间隔多少列
int betweenYRow = 2; // 水印纵向之间间隔多少行
int XCount = 0; // 横向共有水印多少个
int YCount = 0; // 纵向共有水印多少个
int waterRemarkWidth = 0; // 水印图片宽度为多少列
int waterRemarkHeight = 0; // 水印图片高度为多少行
// 校验传入的水印图片格式
if(!waterRemarkPath.EndsWith("png") && !waterRemarkPath.EndsWith("PNG"))
{
//throw new RuntimeException("向Excel上面打印水印，目前支持png格式的图片。");
}
// 加载图片
FileStream fs = new FileStream(waterRemarkPath, FileMode.Open, FileAccess.Read); //将图片以文件流的形式进行保存
BinaryReader br = new BinaryReader(fs);
byte[] imgBytesIn = br.ReadBytes((int)fs.Length); //将流读入到字节数组中
for (int i = 0; i < wb.NumberOfSheets; i++)
{ // wb.getNumberOfSheets()
ISheet sheet = wb.GetSheetAt(0);
sheet.ProtectSheet("Yelioa123");
try
{
XCount = (sheet.GetRow(0).LastCellNum);
if (XCount >= 0 && XCount <= 5)
{
XCount = 1;
}
if (XCount >= 5)
{
XCount = XCount / 2;
}
}
catch (Exception e)
{
XCount = 1;
}
try
{
YCount = sheet.LastRowNum / (betweenYRow); // 纵向共有水印多少个
if (YCount < 4)
{
YCount = 2;
}
}
catch (Exception e)
{
YCount = 50;
}
// 开始打水印
IDrawing drawing = sheet.CreateDrawingPatriarch();
// 按照共需打印多少行水印进行循环
for (int yCount = 0; yCount < YCount; yCount++)
{
// 按照每行需要打印多少个水印进行循环
for (int xCount = 0; xCount < XCount; xCount++)
{
// 创建水印图片位置
int xIndexInteger = startXCol + (xCount * 2) + (xCount * betweenXCol);
int yIndexInteger = startYRow + (yCount * 1) + (yCount * betweenYRow);
/*参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）； 第四个参数是（是y轴的结束节点）；
* 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）； 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）；
* 第七个参数是（图片宽度，共多少列）； 第8个参数是（图片高度，共多少行）；
* */
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, xIndexInteger, yIndexInteger,
xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);
IPicture pic = drawing.CreatePicture(anchor,
wb.AddPicture(imgBytesIn, PictureType.PNG));
pic.Resize();
}
}
}
}
/// <summary>
/// 填充数据到表格
/// </summary>
protected override void FillBody()
{
if (null == _ScrapMerge)
{
return;
}
//
// 填写填表人
//
TempRow = Sheet.GetRow(2);// CreateRow(2);
TempRow.GetCell(5).SetCellValue(_ScrapMerge.FillPerson);
//
// 填写学校名称
//
TempRow = Sheet.GetRow(3);
TempRow.GetCell(1).SetCellValue("测试学校");
//
// 填写信息化负责人和电话
//
TempRow = Sheet.GetRow(4);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.People);
TempRow.GetCell(4).SetCellValue(_ScrapMerge.Tel);
//
// 填写报表详情
//
TempRow = Sheet.GetRow(5);
var aaa = (DateTime.Now.Year.ToString() + "年度学校信息化设备报废明细表").ToString();
TempRow.GetCell(0).SetCellValue(aaa);
//
// 填写名称及品牌行
//
TempRow = Sheet.GetRow(7);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.AssetsName + _ScrapMerge.ModelSpecification);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.Counts);
TempRow.GetCell(3).SetCellValue(_ScrapMerge.GetsDate.ToShortDateString());
TempRow.GetCell(4).SetCellValue(_ScrapMerge.UsedYears);
TempRow.GetCell(5).SetCellValue(_ScrapMerge.DeadYears);
//
// 填写图片上方规格及品牌型号
//
TempRow = Sheet.GetRow(24);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.AssetsName + _ScrapMerge.ModelSpecification);
//
// 分辨四大类
//
switch (_ScrapMerge.type)
{
case 1: // 计算机
{
//
// 填写CPU行
//
TempRow = Sheet.GetRow(9);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.dcpu);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.dzhuban);
TempRow.GetCell(3).SetCellValue(_ScrapMerge.dyingpan);
TempRow.GetCell(4).SetCellValue(_ScrapMerge.dneicun);
TempRow.GetCell(5).SetCellValue(_ScrapMerge.dxianka);
TempRow.GetCell(6).SetCellValue(_ScrapMerge.dguangqu);
//
// 填写CPU行
//
TempRow = Sheet.GetRow(11);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.dxianshiqi);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.dxianshiqichicun);
TempRow.GetCell(3).SetCellValue(_ScrapMerge.dxianshiqileixing);
TempRow.GetCell(4).SetCellValue(_ScrapMerge.djianpan);
TempRow.GetCell(5).SetCellValue(_ScrapMerge.dshubiao);
//
// 时间行
//
TempRow = Sheet.GetRow(14);
var i = ("学校（公章）" + DateTime.Now.ToLongDateString().ToString()).ToString();
TempRow.GetCell(4).SetCellValue(i);
//插入计算机类图片2
//第一步：读取图片到byte数组 (方法二)
byte[] bytes1 = System.IO.File.ReadAllBytes(@_ScrapMerge.PhotoSrcD);
//第二步：将图片添加到workbook中 指定图片格式 返回图片所在workbook->Picture数组中的索引地址（从1开始）
int pictureIdx1 = Workbook.AddPicture(bytes1, PictureType.JPEG);
//第三步：在sheet中创建画部
IDrawing patriarch1 = Sheet.CreateDrawingPatriarch();
//第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）
IClientAnchor anchor1 = patriarch1.CreateAnchor(0, 0, 0, 0, 1, 42, 6, 57);
//第五步：创建图片
IPicture pict1 = patriarch1.CreatePicture(anchor1, pictureIdx1);
break;
}
case 2: //服务器
{
//
// 填写CPU行
//
TempRow = Sheet.GetRow(9);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.fcpu);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.fzhuban);
TempRow.GetCell(3).SetCellValue(_ScrapMerge.fyingpan);
TempRow.GetCell(4).SetCellValue(_ScrapMerge.fneicun);
TempRow.GetCell(5).SetCellValue(_ScrapMerge.fxianka);
TempRow.GetCell(6).SetCellValue(_ScrapMerge.fguangqu);
//
// 时间行
//
TempRow = Sheet.GetRow(12);
var i = ("学校（公章）" + DateTime.Now.ToLongDateString().ToString()).ToString();
TempRow.GetCell(4).SetCellValue(i);
break;
}
case 3: // 投影仪
{
//
// 填写显示技术行
//
TempRow = Sheet.GetRow(9);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.txianshijishu);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.tzuigaoxianshifenbianlv);
TempRow.GetCell(3).SetCellValue(_ScrapMerge.tbiaochengguangliangdu);
TempRow.GetCell(4).SetCellValue(_ScrapMerge.tbiaochengduibidu);
//
// 时间行
//
TempRow = Sheet.GetRow(12);
var i = ("学校（公章）" + DateTime.Now.ToLongDateString().ToString()).ToString();
TempRow.GetCell(4).SetCellValue(i);
break;
}
case 4: // 其他
{
//
// 填写主要参数行
//
TempRow = Sheet.GetRow(9);
TempRow.GetCell(1).SetCellValue(_ScrapMerge.qzhuyaocanshu1);
TempRow.GetCell(2).SetCellValue(_ScrapMerge.qzhuyaocanshu2);
TempRow.GetCell(3).SetCellValue(_ScrapMerge.qzhuyaocanshu3);
TempRow.GetCell(4).SetCellValue(_ScrapMerge.qzhuyaocanshu4);
TempRow.GetCell(5).SetCellValue(_ScrapMerge.qzhuyaocanshu5);
TempRow.GetCell(6).SetCellValue(_ScrapMerge.qzhuyaocanshu6);
//
// 时间行
//
TempRow = Sheet.GetRow(12);
var i = ("学校（公章）" + DateTime.Now.ToLongDateString().ToString()).ToString();
TempRow.GetCell(4).SetCellValue(i);
break;
}
default:
break;
}
//*****************************************************************
//说明：插入图片
//1.创建EXCEL中的Workbook
//IWorkbook myworkbook = new HSSFWorkbook(); //已有（Workbook）
//2.创建Workbook中的Sheet
//ISheet mysheet = myworkbook.CreateSheet("sheet1"); //已有（Sheet）
//第一步：读取图片到byte数组 (方法一)
//HttpWebRequest request = (HttpWebRequest)WebRequest.Create(_ScrapMerge.PhotoSrc); // 有异常，图片路径格式不对
//byte[] bytes;
//using (Stream stream = request.GetResponse().GetResponseStream())
//{
// using (MemoryStream mstream = new MemoryStream())
// {
// int count = 0;
// byte[] buffer = new byte[1024];
// int readNum = 0;
// while ((readNum = stream.Read(buffer, 0, 1024)) > 0)
// {
// count = count + readNum;
// mstream.Write(buffer, 0, 1024);
// }
// mstream.Position = 0;
// using (BinaryReader br = new BinaryReader(mstream))
// {
// bytes = br.ReadBytes(count);
// }
// }
//}
//第一步：读取图片到byte数组 (方法二)
byte[] bytes = System.IO.File.ReadAllBytes(@_ScrapMerge.PhotoSrc);
//第二步：将图片添加到workbook中 指定图片格式 返回图片所在workbook->Picture数组中的索引地址（从1开始）
int pictureIdx = Workbook.AddPicture(bytes, PictureType.JPEG);
//第三步：在sheet中创建画部
IDrawing patriarch = Sheet.CreateDrawingPatriarch();
//第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）
IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, 1, 26, 6, 42);
//第五步：创建图片
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
//6.保存
//FileStream file = new FileStream(@"E:\myworkbook11.xls", FileMode.Create);
//Workbook.Write(file);
//using (FileStream fs = new FileStream(savePath, FileMode.OpenOrCreate))
//{
// Workbook.Write(fs);
// fs.Close(); // 释放
//}
//
// 将临时文件保存到指定路径
//
//scrapMergeExport.Save(savePath);
//*****************************************************************
Thread.Sleep(10);
}
/// <summary>
/// 根据excel实体导出excel ，文件名不需要加后缀
/// </summary>
/// <param name="excelEntity"></param>
/// <param name="fileName"></param>
public static void ExcelEntityToExcel(Dictionary <string, ExcelEntity> excelEntity, string fileName)
{
if (excelEntity == null)
{
return;
}
IWorkbook workbook = null;
CellEntity firstCellEntity = excelEntity.First().Value.CellEntities.FirstOrDefault();
string fileExtend = "";//文件后缀
if (firstCellEntity != null)
{
if (firstCellEntity.Cell is HSSFCell)
{
workbook = new HSSFWorkbook();
fileExtend = ".xls";
}
else if (firstCellEntity.Cell is XSSFCell)
{
workbook = new XSSFWorkbook();
fileExtend = ".xlsx";
}
}
foreach (var sheetName in excelEntity.Keys)
{
ISheet sheet = workbook.CreateSheet(sheetName);
List <CellEntity> tempList = excelEntity[sheetName].CellEntities.OrderBy(x => x.FirstRow).ThenBy(x => x.FirstCol).ToList();
CellEntity firstCell = tempList.First();
if (firstCell == null)
{
continue;
}
int startRow = firstCell.FirstRow;
int startCol = firstCell.FirstCol;
int rowCount = excelEntity[sheetName].CellEntities.Select(x => x.FirstRow).Distinct().Count();
int colCount = excelEntity[sheetName].CellEntities.Select(x => x.FirstCol).Distinct().Count();
for (int rowIndex = startRow; rowIndex < startRow + rowCount; rowIndex++)
{//创建单元格
IRow row = sheet.CreateRow(rowIndex);
for (int colIndex = startCol; colIndex < startCol + colCount; colIndex++)
{
CellEntity entity = excelEntity[sheetName].CellEntities.Find(x => x.FirstRow == rowIndex && x.FirstCol == colIndex);
if (entity != null)
{
ICell cell = row.CreateCell(colIndex);
if (entity.Cell != null)
{
if (entity.IsMerge)
{//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(entity.FirstRow, entity.FirstRow + entity.RowSpan - 1, entity.FirstCol, entity.FirstCol + entity.ColSpan - 1));
}
cell.SetCellType(entity.Cell.CellType);
switch (entity.Cell.CellType)
{
case CellType.Numeric:
cell.SetCellValue(entity.Cell.NumericCellValue);
break;
case CellType.Boolean:
cell.SetCellValue(entity.Cell.BooleanCellValue);
break;
case CellType.Formula:
case CellType.Blank:
case CellType.String:
case CellType.Unknown:
case CellType.Error:
default:
cell.SetCellValue(entity.Cell.ToString());
break;
}
}
//cell.CellStyle = workbook.CreateCellStyle();
//cell.CellStyle.CloneStyleFrom(entity.Cell.CellStyle);
}
}
}
if (excelEntity[sheetName].Pictures != null && excelEntity[sheetName].Pictures.Count > 0)
{
//插入图片
foreach (var item in excelEntity[sheetName].Pictures)
{
int pictureIdx = workbook.AddPicture(item.PictureData, PictureType.JPEG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, item.MinCol, item.MinRow, item.MaxCol, item.MaxRow);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
byte[] buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName + fileExtend, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
}
protected override void Writing(object sender, WriteEventArgs args)
{
Cell cell = args.Entity as Cell;
ISheet exSheet = args.ExSheet;
if (cell == null)
{
return;
}
object tmpObject = this.GetValue();
if (tmpObject == null)
{
return;
}
IWorkbook book = exSheet.Workbook;
//IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();
IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();//只能有一个实例，否则只有最后一个对象生效
int rowIndex = this.RowIndex;
int colIndex = this.ColIndex;
IRow exRow = exSheet.GetRow(rowIndex) ?? exSheet.CreateRow(rowIndex);
//if (exRow != null)
//{
ICell exCell = exRow.GetCell(this.ColIndex) ?? exRow.CreateCell(this.ColIndex);
//if (exCell != null)
//{
//object tmpObject = sheet.IsDynamic ? sheet.GetValue(cell,sheetName) : dt.Rows[cell.DataIndex][cell.Field];
if (cell.ValueAppend)
{
tmpObject = exCell.StringCellValue + tmpObject;
}
if (tmpObject.GetType() == typeof(byte[]))//处理图片
{
CellRangeAddress region = NPOIExcelUtil.GetRange(exCell);
Image image = new Bitmap(new MemoryStream(tmpObject as byte[]));
Size size = image.Size;
if (size.IsEmpty)
{
return;
}
IClientAnchor anchor = region != null?
draw.CreateAnchor(0, 0, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, colIndex, rowIndex, colIndex + this.ColCount, rowIndex + this.RowCount);
IPicture pic = draw.CreatePicture(anchor, book.AddPicture((byte[])tmpObject, PictureType.JPEG));
switch (cell.FillType)
{
case FillType.Origin:
pic.Resize();
break;
case FillType.Scale:
float width = 0, height = 0;
for (int i = anchor.Col1; i < anchor.Col2; i++)
{
width += exSheet.GetColumnWidth(i) / 256f * 12;
}
for (int i = anchor.Row1; i < anchor.Row2; i++)
{
IRow row = exSheet.GetRow(i);
height += row != null ? row.HeightInPoints : exSheet.DefaultRowHeightInPoints;
}
float factor = Math.Min(width / (size.Width * 0.75f), height / (size.Height * 0.75f));
pic.Resize(factor);
break;
default:
break;
}
}
else
{
exCell.SetCellValue((tmpObject ?? "").ToString());
}
//}
//}
}
public void GetClientAnchor()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(10);
ICell cell = row.CreateCell(5);
ICreationHelper factory = wb.GetCreationHelper();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
double r_mul, c_mul;
if (sheet is HSSFSheet)
{
double rowheight = Units.ToEMU(row.HeightInPoints) / Units.EMU_PER_PIXEL;
r_mul = 256.0 / rowheight;
double colwidth = sheet.GetColumnWidthInPixels(2);
c_mul = 1024.0 / colwidth;
}
else
{
r_mul = c_mul = Units.EMU_PER_PIXEL;
}
int dx1 = (int)Math.Round(10 * c_mul);
int dy1 = (int)Math.Round(10 * r_mul);
int dx2 = (int)Math.Round(3 * c_mul);
int dy2 = (int)Math.Round(4 * r_mul);
int col1 = cell.ColumnIndex + 1;
int row1 = row.RowNum;
int col2 = cell.ColumnIndex + 2;
int row2 = row.RowNum + 1;
IClientAnchor anchor = Drawing.CreateAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
IComment comment = Drawing.CreateCellComment(anchor);
comment.Visible = (/*setter*/ true);
cell.CellComment = (/*setter*/ comment);
anchor = comment.ClientAnchor;
Assert.AreEqual(dx1, anchor.Dx1);
Assert.AreEqual(dy1, anchor.Dy1);
Assert.AreEqual(dx2, anchor.Dx2);
Assert.AreEqual(dy2, anchor.Dy2);
Assert.AreEqual(col1, anchor.Col1);
Assert.AreEqual(row1, anchor.Row1);
Assert.AreEqual(col2, anchor.Col2);
Assert.AreEqual(row2, anchor.Row2);
anchor = factory.CreateClientAnchor();
comment = Drawing.CreateCellComment(anchor);
cell.CellComment = (/*setter*/ comment);
anchor = comment.ClientAnchor;
if (sheet is HSSFSheet)
{
Assert.AreEqual(0, anchor.Col1);
Assert.AreEqual(0, anchor.Dx1);
Assert.AreEqual(0, anchor.Row1);
Assert.AreEqual(0, anchor.Dy1);
Assert.AreEqual(0, anchor.Col2);
Assert.AreEqual(0, anchor.Dx2);
Assert.AreEqual(0, anchor.Row2);
Assert.AreEqual(0, anchor.Dy2);
}
else
{
// when anchor is Initialized without parameters, the comment anchor attributes default to
// "1, 15, 0, 2, 3, 15, 3, 16" ... see XSSFVMLDrawing.NewCommentShape()
Assert.AreEqual(1, anchor.Col1);
Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx1);
Assert.AreEqual(0, anchor.Row1);
Assert.AreEqual(2 * Units.EMU_PER_PIXEL, anchor.Dy1);
Assert.AreEqual(3, anchor.Col2);
Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx2);
Assert.AreEqual(3, anchor.Row2);
Assert.AreEqual(16 * Units.EMU_PER_PIXEL, anchor.Dy2);
}
}
/// <summary>
/// 按标准配置填值
/// </summary>
/// <param name="exCell">Excel单元格</param>
/// <param name="field">字段类型</param>
/// <param name="value"></param>
/// <returns>操作状态:是否成功</returns>
public static bool SetCellValue(ICell exCell, Field field, object value)
{
ICellStyle exCellStyle = null;
Type dataType = value.GetType();
IWorkbook book = exCell.Sheet.Workbook;
if (dataType == typeof(string))
{
if (field.Type == FieldType.Formula)
{
exCell.SetCellFormula(value as string);
}
else if (field.Type == FieldType.Text)
{
exCell.SetCellValue(value as string);
}
}
else if (ParseUtil.IsNumberType(dataType) && field.Type == FieldType.Numeric)
{
if (!string.IsNullOrEmpty(field.Format))
{
exCellStyle = exCell.CellStyle ?? book.CreateCellStyle();
exCellStyle.DataFormat = book.CreateDataFormat().GetFormat(field.Format);
exCell.CellStyle = exCellStyle;
}
exCell.SetCellValue(double.Parse(value.ToString()));
}
else if ((dataType == typeof(DateTime) || dataType == typeof(DateTime?)) && field.Type == FieldType.Datetime)
{
//如不给日期指定格式会显示成数字,因此强制使用默认格式:"yyyy/mm/dd"
string tmpStr = string.IsNullOrEmpty(field.Format) ? DEFAULT_DATEFORMAT : field.Format;
exCellStyle = exCell.CellStyle ?? book.CreateCellStyle();
exCellStyle.DataFormat = book.CreateDataFormat().GetFormat(field.Format);
exCell.CellStyle = exCellStyle;
exCell.SetCellValue((DateTime)value);
}
else if ((dataType == typeof(bool) || dataType == typeof(bool?)) && field.Type == FieldType.Boolean)
{
exCell.SetCellValue((bool)value);
}
else if (dataType == typeof(byte[]) && field.Type == FieldType.Picture)
{
//IDrawing draw = exCell.Sheet.DrawingPatriarch ?? exCell.Sheet.CreateDrawingPatriarch();//XSSF未实现DrawingPatriarch接口
IDrawing draw = exCell.Sheet.CreateDrawingPatriarch();
IPicture pic = draw.CreatePicture(draw.CreateAnchor(0, 0, 0, 0, exCell.ColumnIndex, exCell.RowIndex,
exCell.ColumnIndex + field.ColSpan, exCell.RowIndex + 1),
book.AddPicture((byte[])value, PictureType.JPEG));
}
else if ((dataType == typeof(byte) || dataType == typeof(byte?)) && field.Type == FieldType.Error)
{
exCell.SetCellErrorValue((byte)value);
}
else
{
/**
* (1) 如有转换器：想办法将DataType转换成FieldType与之匹配的数据类型
* (2) 否则，默认用字符串类型
*/
return(false);
}
return(true);
}
public void Run()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("linechart");
IRow row;
ICell cell;
// line chart
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");
// ScatterChart
ISheet sheet1 = wb.CreateSheet("ScatterChart");
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
{
row = sheet1.CreateRow((short)rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
{
cell = row.CreateCell((short)colIndex);
cell.SetCellValue(colIndex * (rowIndex + 1));
}
}
drawing = sheet1.CreateDrawingPatriarch();
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = (LegendPosition.TopRight);
IScatterChartData <double, double> data = chart.ChartDataFactory.CreateScatterChartData <double, double>();
IValueAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet1, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet1, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet1, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
data.AddSeries(xs, ys1);
data.AddSeries(xs, ys2);
chart.Plot(data, bottomAxis, leftAxis);
using (FileStream fs = File.Create(@"C:\00.Dev\temp\ChartTest.xlsx"))
{
wb.Write(fs);
}
}
