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