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); }
/*泛类型行不通 * public ISheet pictureDataToSheet<T>(ISheet sheet, T pictureNPOI,int startRow,int startCol, int endRow,int endCol) * // where T: XSSFPicture, HSSFPicture,类型应该只有一种的原因吧,无法执行类型约束为两个类,因为类的约束必须放在第一个 * { * * XSSFPicture pictureNPOI_XSSFPicture = pictureNPOI as XSSFPicture; * HSSFPalette pictureNPOI_HSSFPalette = pictureNPOI as HSSFPalette; * //XSSFPicture,HSSFPalette是类,只能有一种类型,正好是泛类型要解决的 * //方法和使用一样,但是T的类型取决类申城的Isheet的类型 * //应该使用重载 * if (true) * { * * } * else * { * return null; * } * workbook.AddPicture(pictureNPOI.) * } */ //重载 public ISheet pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int startRow, int startCol, int endRow, int endCol) { /*将实际图片转换为pictureData时使用,但是pictureNPOI本身就是picture * byte[] pictureByte= * workbook.AddPicture(, PictureType.PNG); */ //判断是否有sheet //无,则创建 if (sheet == null) { sheet = this.workbook.CreateSheet(); } //执行向sheet写图片 //创建DrawingPatriarch,存放的容器 IDrawing patriarch = sheet.CreateDrawingPatriarch(); ///System.InvalidCastException:“无法将类型为“NPOI.XSSF.UserModel.XSSFDrawing”的对象强制转换为类型“NPOI.HSSF.UserModel.HSSFPatriarch”。” /// HSSFPatriarch patriarch = (HSSFPatriarch)sheetA.CreateDrawingPatriarch(); /// 根据报错改为如下 // IDrawing patriarch = sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, startCol, startRow, endCol, endRow); //将图片文件读入workbook,用索引指向该文件 int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG); //根据读入图片和anchor把图片插到相应的位置 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); return(sheet); }
public static int LoadImage(string path, IWorkbook wb) { FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); byte[] buffer = new byte[file.Length]; file.Read(buffer, 0, (int)file.Length); return wb.AddPicture(buffer, PictureType.JPEG); }
public int LoadImage(string path, IWorkbook wb) { FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); byte[] buffer = new byte[file.Length]; file.Read(buffer, 0, (int)file.Length); return(wb.AddPicture(buffer, PictureType.JPEG)); }
public void insertPicture(string sheetName, Models.RowData modelRowData, System.Windows.Forms.TextBox textBox) { //删除空白的实例 for (int i = modelRowData.instances.Count - 1; i >= 0; i--) { bool isDelete = true; Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i]; for (int j = 0; j < model_Instance.pictures.Count; j++) { if (model_Instance.pictures[j] != null) { isDelete = false; } } if (isDelete) { modelRowData.instances.RemoveAt(i); } } //delete sheet if exist ISheet sheet = null; sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.CreateSheet(sheetName); } HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); for (int i = 0; i < modelRowData.instances.Count; i++) { Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i]; for (int j = 0; j < model_Instance.pictures.Count; j++) { if (model_Instance.pictures[j] != null) { string picturePath = model_Instance.pictures[j].ToString(); //读取图片 byte[] bytes = System.IO.File.ReadAllBytes(picturePath); int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG); //add a picture HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, 4 + (j * 12), 2 + (i * 32), 14 + (j * 12), 10 + (i * 32)); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); textBox.AppendText(" " + picturePath.Substring(picturePath.LastIndexOf("\\") + 1) + "\r\n"); } } } fs = new FileStream(fileName, FileMode.Open, FileAccess.Write); workbook.Write(fs); fs.Close(); textBox.AppendText("Insert pictures succeed\r\n"); }
public void DrawGraphs(IWorkbook workBook, ISheet sheet, IClientAnchor anchor, Byte[] graphData, PictureType type = DefaultPictureType) { Int32 index = workBook.AddPicture(graphData, type); IDrawing patriarch = sheet.CreateDrawingPatriarch(); patriarch.CreatePicture(anchor, index); }
private static void setPic(IWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, int rowline, int col, string webRootPath) { if (string.IsNullOrEmpty(path)) { return; } byte[] bytes = System.IO.File.ReadAllBytes(webRootPath + path); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, rowline, col + 1, rowline + 1); //把图片插到相应的位置 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); }
public static int GetPictureIdx(IWorkbook workbook, byte[] bytes, string url) { string ext = Path.GetExtension(url); int pictureIdx = 0; switch (ext.ToLower()) { case ".png": pictureIdx = workbook.AddPicture(bytes, PictureType.PNG); return(pictureIdx); case ".jpg": pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); return(pictureIdx); case ".jpeg": pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); return(pictureIdx); default: return(pictureIdx); } }
/// <summary> /// 将Base64字符串转换为图片 /// </summary> /// <param name="workbook"></param> /// <param name="bmp">图片</param> /// <param name="col1">图片起始列</param> /// <param name="row1">图片起始行</param> /// <param name="col2">图片结束列</param> /// <param name="row2">图片结束行</param> /// <returns></returns> public static void AddImageToExcel(IWorkbook workbook, int sheetIndex, Bitmap bmp, int col1, int row1, int col2, int row2) { string base64 = ImgToBase64String(bmp); byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+")); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); ISheet sheet = workbook.GetSheetAt(sheetIndex); XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch(); //##处理照片位置,【图片左上角为(6, 2)第2+1行6+1列,右下角为(8, 6)第6+1行8+1列】 XSSFClientAnchor anchor = new XSSFClientAnchor(100 * 10000, 0, 100, 100, col1, row1, col2, row2); patriarch.CreatePicture(anchor, pictureIdx); }
///// <summary> ///// 将Base64字符串转换为图片 ///// </summary> ///// <param name="workbook"></param> ///// <param name="base64">图片base64码</param> ///// <param name="tempPath">模板路径</param> ///// <param name="outputPath">输出路径</param> ///// <param name="col1">图片起始列</param> ///// <param name="row1">图片起始行</param> ///// <param name="col2">图片结束列</param> ///// <param name="row2">图片结束行</param> ///// <returns></returns> //public static string AddImageToExcel(HSSFWorkbook workbook, string base64, string tempPath, string outputPath, int col1, int row1, int col2, int row2) //{ // byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+")); // int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); // HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); // HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); // //##处理照片位置,【图片左上角为(6, 2)第2+1行6+1列,右下角为(8, 6)第6+1行8+1列】 // HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2); // HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); // string outFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); // string resPath = outputPath + outFileName + ".xls"; // return resPath; //} /// <summary> /// 将Base64字符串转换为图片 /// </summary> /// <param name="workbook"></param> /// <param name="base64">图片base64码</param> /// <param name="tempPath">模板路径</param> /// <param name="outputPath">输出路径</param> /// <param name="col1">图片起始列</param> /// <param name="row1">图片起始行</param> /// <param name="col2">图片结束列</param> /// <param name="row2">图片结束行</param> /// <returns></returns> public static string AddImageToExcel(IWorkbook workbook, string base64, string tempPath, string outputPath, int col1, int row1, int col2, int row2) { byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+")); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); ISheet sheet = workbook.GetSheetAt(0); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //##处理照片位置,【图片左上角为(6, 2)第2+1行6+1列,右下角为(8, 6)第6+1行8+1列】 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); string outFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff"); string resPath = outputPath + outFileName + ".xls"; return(resPath); }
/// <summary> /// 往Excel中添加图片 /// </summary> /// <param name="strPicturePath">图片路径(PNG图片)</param> /// <param name="strColumnIndex">将图片添加至第几行(图片的位置,左对齐)</param> public bool InsertPicture(string strPicturePath, int intRowIndex) { bool OK = false; try { if (!File.Exists(strPicturePath)) { return(OK); } //加载图片 FileStream file = new FileStream(strPicturePath, FileMode.Open, FileAccess.Read); byte[] buffer = new byte[file.Length]; file.Read(buffer, 0, (int)file.Length); int intPictureIndex = workbook.AddPicture(buffer, PictureType.PNG); switch (exceltype) { case ExcelType.DEFAULT: string strExtension = Path.GetExtension(FileName).ToLower(); if (strExtension == ".xls") { HSSFCreatePicture(intRowIndex, intPictureIndex); } else if (strExtension == ".xlsx") { XSSFCreatePicture(intRowIndex, intPictureIndex); } break; case ExcelType.XLSX: XSSFCreatePicture(intRowIndex, intPictureIndex); break; case ExcelType.XLS: HSSFCreatePicture(intRowIndex, intPictureIndex); break; } OK = true; } catch (Exception ex) { throw new Exception(ex.Message); } return(OK); }
private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource) { IRow row = null; ICell cell = null; ICellStyle cellStyle = null; IDrawing drawing = null; IPicture picture = null; cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.CENTER; cellStyle.VerticalAlignment = VerticalAlignment.CENTER; for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++) { row = target.CreateRow(rowIndex + 1); for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++) { cell = row.CreateCell(columnIndex); if (dataSource.Columns[columnIndex].DataType == typeof(byte[])) { byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[]; if (image != null && image.Length > 0) { int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG); drawing = target.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1); picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(); } } else { cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString()); } cell.CellStyle = cellStyle; } } }
private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource) { IRow row = null; ICell cell = null; ICellStyle cellStyle = null; IDrawing drawing = null; IPicture picture = null; cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.CENTER; cellStyle.VerticalAlignment = VerticalAlignment.CENTER; for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++) { row = target.CreateRow(rowIndex + 1); for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++) { cell = row.CreateCell(columnIndex); if (dataSource.Columns[columnIndex].DataType == typeof(byte[])) { byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[]; if (image != null && image.Length > 0) { int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG); drawing = target.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1); picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(); } } else { cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString()); } cell.CellStyle = cellStyle; } } }
/// <summary> /// 添加图片 /// </summary> /// <param name="wb">工作簿对象</param> /// <param name="sheet">工作表对象</param> /// <param name="pics">图片列表</param> public void AddPicture(IWorkbook wb, ISheet sheet, List <ExcelPictureModel> pics) { var pictureIdx = 0; var pic = new ExcelPictureModel(); for (int i = 0; i < pics.Count; i++) { pic = pics[i]; pictureIdx = wb.AddPicture(pic.Datas, pic.PicType); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, pic.Weight, pic.Height, pic.StartCol, pic.StartRow, pic.EndCol, pic.EndRow); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); if (pic.IsResize) { pict.Resize(); } } }
/// <summary> /// 插入图片,切设置图片显示范围。 /// </summary> /// <param name = "PicturePath">图片的绝对物理路径</param> /// <param name = "pictureType">图片类型</param> /// <param name = "startColNo">左上角所在列号(从0开始)</param> /// <param name = "startRowNo">左上角所在行号(从0开始)</param> /// <param name = "endColNo">右下角所在列号(从0开始)</param> /// <param name = "endRowNo">右下角所在行号(从0开始)</param> /// <param name = "originalSize">是否按图片原始大小显示,如果是的话传入的右下角参数无效</param> public bool InsertPicture(string picturePath, PictureType pictureType, int startColNo, int startRowNo, int endColNo, int endRowNo, int iSheetNO, bool originalSize = false) { try { if (!File.Exists(picturePath)) { return(false); } if (originalSize) { endColNo = startColNo + 1; endRowNo = startRowNo + 1; } //将图片加入Workbook byte[] bytes = System.IO.File.ReadAllBytes(picturePath); int pictureIdx1 = workbook.AddPicture(bytes, PictureType.JPEG); //获取存在的Sheet,必须在AddPicture之后 if (patriarch == null) { patriarch = (HSSFPatriarch)listSheet[iSheetNO].CreateDrawingPatriarch(); } //插入图片 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, startColNo, startRowNo, endColNo, endRowNo); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx1); if (originalSize) { pict.Resize(); } } catch (System.Exception ex) { LogHelper.Instance.WriteError("导出Excel插入图片失败", ex); return(false); } return(true); }
private static void CreateChartDataSheet(Stream stream, IWorkbook workbook) { var sheet = workbook.CreateSheet("Chart"); var drawing = sheet.CreateDrawingPatriarch(); var data = new byte[stream.Length]; stream.Read(data, 0, (int)stream.Length); var picInd = workbook.AddPicture(data, PictureType.PNG); XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper; XSSFClientAnchor anchor = helper?.CreateClientAnchor() as XSSFClientAnchor; if (anchor != null) { anchor.Col1 = 0; anchor.Row1 = 0; var pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture; pict?.Resize(); } }
public 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); }
private void handleResize(IWorkbook wb, ISheet sheet, IRow row) { IDrawing Drawing = sheet.CreateDrawingPatriarch(); ICreationHelper CreateHelper = wb.GetCreationHelper(); byte[] bytes = HSSFITestDataProvider.Instance.GetTestDataFileContent("logoKarmokar4.png"); row.HeightInPoints = (/*setter*/ GetImageSize(bytes).Y); int pictureIdx = wb.AddPicture(bytes, PictureType.PNG); //add a picture shape IClientAnchor anchor = CreateHelper.CreateClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.Col1 = (/*setter*/ 0); anchor.Row1 = (/*setter*/ 0); IPicture pict = Drawing.CreatePicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.Resize(); }
public void TableToExcel(DataTable dt, Image img) { if (Path.GetExtension(fileName) == ".xls") { workbook = new HSSFWorkbook(); } else if (Path.GetExtension(fileName) == ".xlsx") { workbook = new XSSFWorkbook(); } else { return; } ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName); MemoryStream ms = new MemoryStream(); img.Save(ms, System.Drawing.Imaging.ImageFormat.Png); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 2, 2, 14, 34); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, workbook.AddPicture(ms.ToArray(), PictureType.PNG)); pict.Resize(); ms.Close(); //表头 IRow row = sheet.CreateRow(anchor.Row2 + 1); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } //行数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow ir = sheet.CreateRow(i + anchor.Row2 + 2); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = ir.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } }
//public string ExportSKU(IEnumerable<CMS_SKU> SKUList, User_Profile_Model userInfo) public string ExportSKU(IEnumerable <Export2Excel> skuList, User_Profile_Model userInfo) { var exportPath = System.Web.HttpContext.Current.Server.MapPath("~/MediaLib/"); IWorkbook workbook = null; using (var file = new FileStream(exportPath + @"/SKUData_Tpl.xlsx", FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(file); } var sheet1 = workbook.GetSheet("Sheet1"); var i = 0; foreach (Export2Excel SKU in skuList) { i++; //第一行是标题列 忽略 var curRow = sheet1.CreateRow(i); curRow.HeightInPoints = 151; //设置单元格的高度,用于支持图像的显示 //主显图像 var firstPic = "NoPicture"; var j = 0; curRow.CreateCell(j++).SetCellValue(i);//显然 这时候的J还是0... if (!string.IsNullOrEmpty(SKU.ImgName)) { firstPic = ConfigurationManager.AppSettings["CMSImgUrl"] + SKU.HMNUM + "/" + SKU.ImgName + SKU.FileFormat; var physicalPath = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["ImageStoragePath"]); var firstPicSamll = physicalPath + SKU.HMNUM + "/" + SKU.ImgName + "_th" + SKU.FileFormat; if (File.Exists(firstPicSamll)) { //IDrawing patriarch = sheet1.CreateDrawingPatriarch(); ////create the anchor //XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, i, 1, i); ////anchor.AnchorType = 2; ////load the picture and get the picture index in the workbook //XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage(firstPic_Samll, workbook)); ////Reset the image to the original size. ////picture.Resize(); //Note: Resize will reset client anchor you set. //picture.LineStyle = LineStyle.DashDotGel; var bytes = File.ReadAllBytes(firstPicSamll); var pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // Create the drawing patriarch. This is the top level container for all shapes. var patriarch = sheet1.CreateDrawingPatriarch(); var anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, i, 1, i);//coloum固定是1 var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); } } curRow.CreateCell(j++).SetCellValue(firstPic); curRow.CreateCell(j++).SetCellValue(SKU.SKU); curRow.CreateCell(j++).SetCellValue(SKU.ChannelName); curRow.CreateCell(j++).SetCellValue(SKU.ProductName); curRow.CreateCell(j++).SetCellValue(SKU.BrandName); curRow.CreateCell(j++).SetCellValue(SKU.UPC); var landedCost = SKU.FirstCost.ConvertToNotNull() + SKU.OceanFreight.ConvertToNotNull() + SKU.USAHandlingCharge.ConvertToNotNull() + SKU.Drayage.ConvertToNotNull(); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(landedCost)); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.FirstCost.ConvertToNotNull())); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.OceanFreight.ConvertToNotNull())); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.Drayage.ConvertToNotNull())); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.USAHandlingCharge.ConvertToNotNull())); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.EstimateFreight)); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.SalePrice)); curRow.CreateCell(j++).SetCellValue(Convert.ToDouble(SKU.RetailPrice)); //curRow.CreateCell(j++).SetCellValue(SKU.MaterialName == null ? "NONE" : SKU.MaterialName); //curRow.CreateCell(j++).SetCellValue(SKU.ColourName == null ? "NONE" : SKU.ColourName); curRow.CreateCell(j++).SetCellValue(SKU.MaterialName ?? "NONE"); curRow.CreateCell(j++).SetCellValue(SKU.ColourName ?? "NONE"); curRow.CreateCell(j++).SetCellValue(SKU.HMNUM); curRow.CreateCell(j++).SetCellValue(SKU.Pieces); curRow.CreateCell(j++).SetCellValue(SKU.MasterPack); var boxNum = SKU.Pieces / SKU.MasterPack; curRow.CreateCell(j++).SetCellValue(boxNum); curRow.CreateCell(j++).SetCellValue(SKU.ProductName); curRow.CreateCell(j++).SetCellValue(SKU.StockKey); curRow.CreateCell(j++).SetCellValue(SKU.StockkeyQTY.ConvertToNotNull()); var strWeight = ""; var strCtn = ""; foreach (var cCtn in SKU.CMS_HMNUM.CMS_ProductCTN)//view里面如果存在HMNUM,那么CMS_HMNUM也100%存在这个HMNUM,所以不需要做NULL判断 2014年5月2日 { strWeight = cCtn.CTNTitle + ": " + (cCtn.CTNWeight.ConvertToNotNull() * boxNum).ToString() + "\n"; strCtn += cCtn.CTNTitle + ": " + cCtn.CTNLength + "X" + cCtn.CTNWidth + "X" + cCtn.CTNHeight + " \n"; } ; curRow.CreateCell(j++).SetCellValue(strWeight); curRow.CreateCell(j++).SetCellValue(strCtn); //var strDim = ""; //foreach (var cDim in SKU.CMS_HMNUM.CMS_ProductDimension) //{ // strDim += cDim.DimTitle + ": " + cDim.DimLength + "X" + cDim.DimWidth + "X" + cDim.DimHeight + "\n"; //}; var strDim = SKU.CMS_HMNUM.CMS_ProductDimension .Aggregate("", (current, cDim) => current + (cDim.DimTitle + ": " + cDim.DimLength + "X" + cDim.DimWidth + "X" + cDim.DimHeight + "\n")); ; curRow.CreateCell(j++).SetCellValue(strDim); curRow.CreateCell(j++).SetCellValue(SKU.ParentCategoryName ?? "NONE"); curRow.CreateCell(j++).SetCellValue(SKU.SubCategoryName ?? "NONE"); curRow.CreateCell(j++).SetCellValue(SKU.StatusName); curRow.CreateCell(j++).SetCellValue(SKU.ProductDesc); curRow.CreateCell(j++).SetCellValue(SKU.Specifications); curRow.CreateCell(j++).SetCellValue(SKU.Keywords); string strVis; switch (SKU.Visibility.ConvertToNotNull()) { case 0: strVis = "Online"; break; case 1: strVis = "Offline"; break; case 2: strVis = "Online"; break; default: strVis = "Discontinue"; break; } curRow.CreateCell(j++).SetCellValue(strVis); curRow.CreateCell(j++).SetCellValue(SKU.SKU_QTY); curRow.CreateCell(j++).SetCellValue(SKU.URL); //Force excel to recalculate all the formula while open sheet1.ForceFormulaRecalculation = true; } var timeStamp = DateTime.Now.ToString("yyyyMMddHHmmss"); var excelName = userInfo.User_Account + timeStamp + ".xlsx"; var curUserExcle = exportPath + excelName; //if (File.Exists(curUserExcle)) //{ // //有存在则先清掉,避免第一次导N行数据,第二次导出M行数据,当M<N的时候,该文件里面依然保留有N行旧有的数据(那些未被覆盖的行数)2014年5月5日 // File.Delete(curUserExcle); //} using (var file = new FileStream(curUserExcle, FileMode.OpenOrCreate)) { workbook.Write(file); } return(excelName); }
private void handleResize(IWorkbook wb, ISheet sheet, IRow row) { IDrawing Drawing = sheet.CreateDrawingPatriarch(); ICreationHelper CreateHelper = wb.GetCreationHelper(); byte[] bytes = HSSFITestDataProvider.Instance.GetTestDataFileContent("logoKarmokar4.png"); row.HeightInPoints = (/*setter*/GetImageSize(bytes).Y); int pictureIdx = wb.AddPicture(bytes, PictureType.PNG); //add a picture shape IClientAnchor anchor = CreateHelper.CreateClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.Col1 = (/*setter*/0); anchor.Row1 = (/*setter*/0); IPicture pict = Drawing.CreatePicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.Resize(); }
/// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="imgBase64">图片</param> /// <param name="headName">表头名称</param> /// <param name="now">导出时间(当前时间)</param> /// <param name="filter">筛选条件</param> /// <returns></returns> public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string imgBase64, string headName, DateTime now, string filter = "") { int i = 0; int j = 0; int count = 0; ISheet sheet = null; ISheet sheet0 = null; fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook = new HSSFWorkbook(); try { #region sheet1添加图片 if (!string.IsNullOrEmpty(imgBase64)) { try { byte[] bytes2 = Convert.FromBase64String(imgBase64.Replace("data:image/png;base64,", "")); int pictureIdx = workbook.AddPicture(bytes2, PictureType.JPEG); //create sheet sheet0 = workbook.CreateSheet("统计图"); // Create the drawing patriarch. This is the top level container for all shapes. var patriarch = sheet0.CreateDrawingPatriarch(); //add a picture HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3); var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); } catch (Exception) { } } #endregion //添加数据 if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return(-1); } #region 写入表头跟创建时间 //写入表头跟创建时间 if (!String.IsNullOrEmpty(headName)) { IRow row = sheet.CreateRow(count++); int colums = data.Columns.Count; //row.CreateCell(0).SetCellValue(headName); row.Height = 30 * 20; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, (colums - 1) > 0 ? (colums - 1) : colums)); row.CreateCell(0).SetCellValue(headName); ICellStyle style = workbook.CreateCellStyle(); //设置单元格的样式:水平对齐居中 style.Alignment = HorizontalAlignment.Center; //新建一个字体样式对象 IFont font = workbook.CreateFont(); //设置字体加粗样式 font.Boldweight = short.MaxValue; font.FontHeightInPoints = 18; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 row.Cells[0].CellStyle = style; if (!string.IsNullOrEmpty(filter)) { IRow rowFilter = sheet.CreateRow(count++); rowFilter.CreateCell(0).SetCellValue($"筛选条件:{filter}"); sheet.AddMergedRegion(new CellRangeAddress(count - 1, count - 1, 0, (colums - 1) > 0 ? (colums - 1) : colums)); } IRow rowTime = sheet.CreateRow(count++); rowTime.CreateCell(0).SetCellValue($"导出时间:{now.ToString("yyyy-MM-dd HH:mm:ss")}"); sheet.AddMergedRegion(new CellRangeAddress(count - 1, count - 1, 0, (colums - 1) > 0 ? (colums - 1) : colums)); } #endregion if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(count++); ICellStyle style = workbook.CreateCellStyle(); //设置单元格的样式:水平对齐居中 style.Alignment = HorizontalAlignment.Center; //新建一个字体样式对象 IFont font = workbook.CreateFont(); //设置字体加粗样式 font.Boldweight = short.MaxValue; style.SetFont(font); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); row.Cells[j].CellStyle = style; } } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { if (double.TryParse(data.Rows[i][j].ToString(), out double nx)) { row.CreateCell(j).SetCellValue(nx); } //else if (DateTime.TryParse(data.Rows[i][j].ToString(), out DateTime zz)) //{ // row.CreateCell(j).SetCellValue(zz.ToString("yyyy-MM-dd HH:mm:ss")); //} else { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } } ++count; } workbook.Write(fs); //写入到excel return(count); } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return(-1); } }
private static void WriteRow(Object item, IWorkbook book, ISheet sheet, int rowIndex) { Type type = item.GetType(); IRow row = sheet.CreateRow(rowIndex); int cellIndex = 0; type.GetProperties().ToList().ForEach(a => { AssitCellAttribute attr = ExcelAssit.GetCellAttribute(a); if (attr == null) { return; } ICell cell = ExcelAssit.CreateCell(attr, row, cellIndex); cellIndex++; if (attr.CellType == CellType.Image) { if (a.GetValue(item) == null) { return; } byte[] bytes = null; if (a.PropertyType == typeof(string)) { bytes = System.IO.File.ReadAllBytes(a.GetValue(item).ToString()); } if (a.PropertyType == typeof(byte[])) { bytes = (byte[])a.GetValue(item); } int pictureIdx = book.AddPicture(bytes, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cellIndex - 1, rowIndex, cellIndex, rowIndex + 1); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); return; } if (attr.CellType == CellType.Int) { if (a.GetValue(item) == null) { return; } cell.SetCellValue(int.Parse(a.GetValue(item).ToString())); return; } if (attr.CellType == CellType.Float) { if (a.GetValue(item) == null) { return; } cell.SetCellValue(float.Parse(a.GetValue(item).ToString())); return; } if (attr.CellType == CellType.DateTime) { if (a.GetValue(item) == null) { return; } if (a.PropertyType == typeof(string)) { cell.SetCellValue(int.Parse(a.GetValue(item).ToString())); } if (a.PropertyType == typeof(DateTime)) { cell.SetCellValue(DateTime.Parse(a.GetValue(item).ToString()).ToString("yyyy-MM-dd hh:mm:ss")); } } if (attr.CellType == CellType.String) { if (a.GetValue(item) == null) { return; } cell.SetCellValue(a.GetValue(item).ToString()); return; } }); }
/// <summary> /// DataTable 数据添加到Excel模板中. /// </summary> /// <param name="path"></param> /// <param name="dt">输入DataTable</param> /// <param name="startRow">NPOI开始的行号,从0开始.(对应的Excel列从0开始行).</param> /// <param name="rowHeight">行高.(Excel中设置的值,方法中已进行x20计算)</param> /// <param name="styleCell_row">样式单元的行号,默认0行.</param> /// <param name="styleCell_col">样式单元的列号,默认0列.</param> /// <returns></returns> public static string CreateExcelForDataTable(string path, DataTable dt, int startRow = 0, int rowHeight = 0, int styleCell_row = 0, int styleCell_col = 0, Dictionary <string, string> replaceStr = null) { try { //将文件读到内存,在内存中操作excel //打开Excel IWorkbook workbook = OpenExcel(path); ISheet sheet = workbook.GetSheetAt(0);//.a.CreateSheet("Sheet0");//创建一个名称为Sheet0的表 #region 替换表单中指定字符串 if (replaceStr != null) { for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { for (int j = row.FirstCellNum; j < sheet.GetRow(i).LastCellNum; j++) { string str = row.GetCell(j).StringCellValue; foreach (string k in replaceStr.Keys) { if (str.Contains(k)) { row.GetCell(j).SetCellValue(str.Replace(k, replaceStr[k])); replaceStr.Remove(k); break; } } } } } } #endregion //插入行.第"startRow"到第"sheet.LastRowNum"行移动"dt.Rows.Count"行,bool copyRowHeight, bool resetOriginalRowHeight sheet.ShiftRows(startRow, sheet.LastRowNum, dt.Rows.Count, true, true); foreach (DataRow dr in dt.Rows) { IRow excelRow = sheet.CreateRow(startRow); //设置行高 ,excel行高度每个像素点是1/20 if (rowHeight > 0) { excelRow.Height = (short)(rowHeight * 20); } //int curStartCol = startCol; for (int j = 0; j < dt.Columns.Count; j++) { var excelCell = excelRow.CreateCell(j, CellType.String); excelCell.CellStyle = sheet.GetRow(styleCell_row).Cells[styleCell_col].CellStyle; var value = dr[j].ToString(); if (File.Exists(value)) { byte[] bytes = File.ReadAllBytes(value); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) //函数原型:XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2); //坐标(col1,row1)表示图片左上角所在单元格的位置,均从0开始,比如(5,2)表示(第五列,第三行),即F3;注意:图片左上角坐标与(col1,row1)单元格左上角坐标重合 //坐标(col2,row2)表示图片右下角所在单元格的位置,均从0开始,比如(10,3)表示(第十一列,第四行),即K4;注意:图片右下角坐标与(col2,row2)单元格左上角坐标重合 //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移);(dx1,dy1)的最大值为(1023, 255),为一个单元格的大小 //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移);(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小 //参数的解析: HSSFClientAnchor(int dx1,int dy1,int dx2,int dy2,int col1,int row1,int col2,int row2) //dx1: 图片左边相对excel格的位置(x偏移) 范围值为: 0~1023; 即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一 //dy1: 图片上方相对excel格的位置(y偏移) 范围值为: 0~256 原理同上。 //dx2: 图片右边相对excel格的位置(x偏移) 范围值为: 0~1023; 原理同上。 //dy2: 图片下方相对excel格的位置(y偏移) 范围值为: 0~256 原理同上。 //col1和row1: 图片左上角的位置,以excel单元格为参考,比喻这两个值为(1, 1),那么图片左上角的位置就是excel表(1, 1)单元格的右下角的点(A, 1)右下角的点。 //col2和row2: 图片右下角的位置,以excel单元格为参考,比喻这两个值为(2, 2),那么图片右下角的位置就是excel表(2, 2)单元格的右下角的点(B, 2)右下角的点。 IClientAnchor anchor = null; if (path.IndexOf(".xlsx") > 0) { // 2007版本 anchor = new XSSFClientAnchor(0, 0, 0, 0, j, startRow, j + 1, startRow + 1); } else if (path.IndexOf(".xls") > 0) { // 2003版本 anchor = new HSSFClientAnchor(0, 0, 0, 0, j, startRow, j + 1, startRow + 1); } //把图片插到相应的位置 IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); } else { excelCell.SetCellValue(value); } } startRow++; } sheet.ForceFormulaRecalculation = true; //保存Excel SaveAsExel(workbook, path); return(""); } catch (Exception ex) { return(ex.Message); } }
/// <summary> /// 按标准配置填值 /// </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); }
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()); } //} //} }
private static int LoadImage(Stream stream, IWorkbook wb) { byte[] buffer = new byte[stream.Length]; stream.Read(buffer, 0, (int)stream.Length); return(wb.AddPicture(buffer, PictureType.JPEG)); }
private static void InitRowData(DataTable dtSource, ICellStyle tdStyle, DataRow row, IRow dataRow, bool isCell, IWorkbook workbook = null, int rowNum = 0, string reportType = "") { tdStyle.WrapText = true; if (ExportPictureTiltes.Contains(reportType)) { dataRow.Height = 2000; } for (int i = 0; i < dtSource.Columns.Count; i++) { var column = dtSource.Columns[i]; ICell newCell = dataRow.CreateCell(column.Ordinal); if (isCell) { newCell.CellStyle = tdStyle; } string drValue = row[column].ToString(); newCell.SetCellValue(drValue); if (column.ColumnName == "款号图片" && !string.IsNullOrWhiteSpace(drValue)) { #region 款号图片 System.Drawing.Image originalImage = null; ////新建一个bmp图片 //System.Drawing.Image bitmap = null; ////新建一个画板 //System.Drawing.Graphics g = null; //double toheight = 100; //double towidth = 100; if (File.Exists(drValue)) { originalImage = System.Drawing.Image.FromFile(drValue); //double proportion1; //double proportion2; //int x = 0; //int y = 0; ////原图的宽 //int ow = originalImage.Width; ////原图的高 //int oh = originalImage.Height; //towidth = toheight * ow / oh; //proportion1 = toheight / Convert.ToDouble(oh); //proportion2 = towidth / Convert.ToDouble(ow); ////新建一个bmp图片 //bitmap = new System.Drawing.Bitmap(Convert.ToInt32(towidth), Convert.ToInt32(toheight)); ////新建一个画板 //g = System.Drawing.Graphics.FromImage(bitmap); ////设置高质量插值法 //g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High; ////设置高质量,低速度呈现平滑程度 //g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality; ////清空画布并以透明背景色填充 //g.Clear(System.Drawing.Color.Transparent); ////在指定位置并且按指定大小绘制原图片的指定部分 //g.DrawImage(originalImage, new System.Drawing.Rectangle(0, 0, Convert.ToInt32(towidth), Convert.ToInt32(toheight)), new System.Drawing.Rectangle(x, y, ow, oh), System.Drawing.GraphicsUnit.Pixel); ////以jpg格式保存缩略图WebControls ////File.Delete(thumbnailPath); //MemoryStream stream = new MemoryStream(); //bitmap.Save(stream, ImageFormat.Jpeg); //bitmap.Dispose(); ImageConverter imgconv = new ImageConverter(); byte[] imgByte = (byte[])imgconv.ConvertTo(originalImage, typeof(byte[])); int pictureIdx = workbook.AddPicture(imgByte, PictureType.PNG); IDrawing patriarch = newCell.Sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5, 1023, 250, i, rowNum, i, rowNum); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.LineWidth = 100; //pict.Resize(); } else { newCell.SetCellValue("图片不存在"); } #endregion } } }
public void Serialize <T>(IEnumerable <T> dataList) { if (!dataList.Any()) { return; } var type = typeof(T); var worksheetAttribute = (ExcelWorksheet)type.GetCustomAttributes().First(attr => attr.GetType() == typeof(ExcelWorksheet)); var worksheetName = worksheetAttribute.SheetName; var worksheet = _workbook.CreateSheet(worksheetName); var dataFormat = _workbook.CreateDataFormat(); var patriarch = worksheet.CreateDrawingPatriarch(); var headerRowStyle = _workbook.CreateCellStyle(); var font = _workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; font.FontHeightInPoints = 12; font.Color = HSSFColor.White.Index; headerRowStyle.SetFont(font); headerRowStyle.Alignment = HorizontalAlignment.Center; headerRowStyle.FillForegroundColor = HSSFColor.RoyalBlue.Index; headerRowStyle.FillPattern = FillPattern.SolidForeground; var dataProperties = type.GetProperties().OrderBy(prop => { var columnAttribute = (ExcelColumn)prop.GetCustomAttributes().First(attr => attr.GetType() == typeof(ExcelColumn)); return(columnAttribute.Index); }); var rowIndex = 0; var columnIndex = 0; var row = worksheet.CreateRow(rowIndex++); foreach (var dataProperty in dataProperties) { var columnAttribute = (ExcelColumn)dataProperty.GetCustomAttributes() .First(attr => attr.GetType() == typeof(ExcelColumn)); var cell = row.CreateCell(columnIndex++); cell.SetCellType(CellType.String); cell.SetCellValue(columnAttribute.HeaderName); cell.CellStyle = headerRowStyle; } worksheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataProperties.Count() - 1)); foreach (var data in dataList) { row = worksheet.CreateRow(rowIndex++); columnIndex = 0; foreach (var dataProperty in dataProperties) { var columnAttribute = (ExcelColumn)dataProperty.GetCustomAttributes() .First(attr => attr.GetType() == typeof(ExcelColumn)); var dataValue = dataProperty.GetValue(data); var cell = row.CreateCell(columnIndex++); var cellStyle = _workbook.CreateCellStyle(); cellStyle.DataFormat = dataFormat.GetFormat(columnAttribute.DataFormat); cell.CellStyle = cellStyle; if (columnAttribute.IsList) { var outputList = (from object v in (IEnumerable)dataValue select Convert.ToString(v)).ToList(); cell.SetCellValue(string.Join(", ", outputList)); } else if (columnAttribute.IsImageLink) { using (var webClient = new WebClient()) { try { var image = Image.FromStream(webClient.OpenRead(new Uri(Convert.ToString(dataValue)))); var sizeRatio = ((decimal)image.Height / image.Width); var thumbHeight = 100; var thumbWidth = decimal.ToInt32(sizeRatio * thumbHeight); var thumbStream = image.GetThumbnailImage(thumbWidth, thumbHeight, () => false, IntPtr.Zero); var memoryStream = new MemoryStream(); thumbStream.Save(memoryStream, ImageFormat.Jpeg); var pictureIndex = _workbook.AddPicture(memoryStream.ToArray(), PictureType.JPEG); var anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, 0, 0); var picture = patriarch.CreatePicture(anchor, pictureIndex); var size = picture.GetImageDimension(); row.HeightInPoints = size.Height; picture.Resize(); anchor.Dx1 = 5; anchor.Dy1 = 2; } catch (Exception ex) { cell.SetCellValue(ex.Message); } } } else if (columnAttribute.IsLink) { var hlinkStyle = _workbook.CreateCellStyle(); var hlinkFont = _workbook.CreateFont(); hlinkFont.Underline = FontUnderlineType.Single; hlinkFont.Color = HSSFColor.Blue.Index; hlinkStyle.SetFont(hlinkFont); var link = new XSSFHyperlink(HyperlinkType.Url) { Address = (Convert.ToString(dataValue)) }; cell.Hyperlink = (link); cell.SetCellValue(Convert.ToString(dataValue)); cell.CellStyle = (hlinkStyle); } else { if (dataValue is bool) { cell.SetCellValue((bool)dataValue); cell.SetCellType(CellType.Boolean); } else if (dataValue is int) { cell.SetCellValue((int)dataValue); cell.SetCellType(CellType.Numeric); } else if (dataValue is long) { cell.SetCellValue((long)dataValue); cell.SetCellType(CellType.Numeric); } else if (dataValue is float) { cell.SetCellValue((float)dataValue); cell.SetCellType(CellType.Numeric); } else if (dataValue is double) { cell.SetCellValue((double)dataValue); cell.SetCellType(CellType.Numeric); } else if (dataValue is DateTime) { cell.SetCellValue((DateTime)dataValue); } else { cell.SetCellValue(Convert.ToString(dataValue)); cell.SetCellType(CellType.String); } } } } for (var i = 0; i < columnIndex; i++) { worksheet.AutoSizeColumn(i); } }