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); } }