public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing Drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
byte[] wmfData = Encoding.UTF8.GetBytes("test wmf data");
byte[] pngData = Encoding.UTF8.GetBytes("test png data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
int wmfIdx = wb.AddPicture(wmfData, PictureType.WMF);
Assert.AreEqual(2, pictures.Count);
Assert.AreEqual("wmf", ((XSSFPictureData)pictures[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures[wmfIdx]).Data));
int pngIdx = wb.AddPicture(pngData, PictureType.PNG);
Assert.AreEqual(3, pictures.Count);
Assert.AreEqual("png", ((XSSFPictureData)pictures[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures[pngIdx]).Data));
//TODO finish usermodel API for XSSFPicture
XSSFPicture p1 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), jpegIdx);
Assert.IsNotNull(p1);
XSSFPicture p2 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), wmfIdx);
Assert.IsNotNull(p1);
XSSFPicture p3 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), pngIdx);
Assert.IsNotNull(p1);
//check that the Added pictures are accessible After write
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
IList pictures2 = wb.GetAllPictures();
Assert.AreEqual(3, pictures2.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures2[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures2[jpegIdx]).Data));
Assert.AreEqual("wmf", ((XSSFPictureData)pictures2[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures2[wmfIdx]).Data));
Assert.AreEqual("png", ((XSSFPictureData)pictures2[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures2[pngIdx]).Data));
}
public void multiRelationShips()
{
XSSFWorkbook wb = new XSSFWorkbook();
byte[] pic1Data = Encoding.UTF8.GetBytes("test jpeg data");
byte[] pic2Data = Encoding.UTF8.GetBytes("test png data");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(0, pictures.Count);
int pic1 = wb.AddPicture(pic1Data, XSSFWorkbook.PICTURE_TYPE_JPEG);
int pic2 = wb.AddPicture(pic2Data, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFSheet sheet1 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape1 = drawing1.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
XSSFPicture shape2 = drawing1.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFSheet sheet2 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape3 = drawing2.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFPicture shape4 = drawing2.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
Assert.AreEqual(2, pictures.Count);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(2, pictures.Count);
sheet1 = wb.GetSheetAt(0) as XSSFSheet;
drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape11 = (XSSFPicture)drawing1.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape1.PictureData.Data, shape11.PictureData.Data));
XSSFPicture shape22 = (XSSFPicture)drawing1.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape2.PictureData.Data, shape22.PictureData.Data));
sheet2 = wb.GetSheetAt(1) as XSSFSheet;
drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape33 = (XSSFPicture)drawing2.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape3.PictureData.Data, shape33.PictureData.Data));
XSSFPicture shape44 = (XSSFPicture)drawing2.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape4.PictureData.Data, shape44.PictureData.Data));
}
public void Create()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int row, int column, int rowEnd, int columnEnd)
{
try
{
if (_myExcel is HSSFWorkbook)
{
HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
else if (_myExcel is XSSFWorkbook)
{
XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
}
catch (Exception exception)
{
//Log.Write.Error("InsertPictures to file fail", exception);
}
}
private void exportXLSX_Click(object sender, RoutedEventArgs e)
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
// XSSFWorkbook : *.xlsx >= Excel2007
// HSSFWorkbook : *.xls < Excel2007
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet Name");
// 所有索引都从0开始
// 合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex); //创建行
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content");
sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
rowIndex++;
// 插入图片
byte[] data = File.ReadAllBytes(@"image.jpg");
int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor;
anchor.Col1 = 10;
anchor.Row1 = 0;
XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict.Resize();
// 新建sheet
var sheet2 = workbook.CreateSheet("Sheet2");
// 更改样式
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
//保存
workbook.Write(fs);
}
txtStatus.Text = "writing xlsx successful!";
}
/// <summary>
/// XLSX类型Excle文件添加图片
/// </summary>
/// <param name="intRowIndex">插入图片的行数</param>
/// <param name="pictureIndex">图片的顺序</param>
private void XSSFCreatePicture(int intRowIndex, int pictureIndex)
{
XSSFDrawing xssfDrawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.Row1 = intRowIndex;
//anchor.AnchorType = AnchorType2;
XSSFPicture picture = (XSSFPicture)xssfDrawing.CreatePicture(anchor, pictureIndex);
picture.LineStyle = LineStyle.Solid;
picture.Resize();//显示图片的原始尺寸
}
public void IncrementShapeId()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
byte[] jpegData = Encoding.UTF8.GetBytes("picture1");
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape1 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)1, shape1.GetCTPicture().nvPicPr.cNvPr.id);
jpegData = Encoding.UTF8.GetBytes("picture2");
jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape2 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)2, shape2.GetCTPicture().nvPicPr.cNvPr.id);
}
private void setPic(XSSFWorkbook workbook, XSSFDrawing patriarch, string path, ISheet sheet, int rowline, int col, int rowMergerPlus = 0, int colMergerPlus = 0)
{
if (string.IsNullOrEmpty(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, rowline, col + colMergerPlus, rowline + rowMergerPlus);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
/// <summary>
/// 将Base64字符串转换为图片
/// </summary>
/// <param name="sheet"></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(ISheet sheet, Bitmap bmp, int col1, int row1, int col2, int row2)
{
string base64 = ImgToBase64String(bmp);
byte[] bytes = Convert.FromBase64String(base64.Replace(" ", "+"));
int pictureIdx = sheet.Workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
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>
/// .xlsx后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSXExcel(string excelPath, string imgPath)
{
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
{
//创建工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
//获取第一个工作表（下标从0起）
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
//获取指定图片的字节流
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG);
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：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),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(excelPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
xssfworkbook.Write(file);
//关闭工作簿
xssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void TestReadAnchors()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4);
XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape;
Assert.IsNotNull(shape1);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5);
XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape;
Assert.IsNotNull(shape2);
int pictureIndex = wb1.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6);
XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape;
Assert.IsNotNull(shape3);
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(3, shapes.Count);
Assert.AreEqual(shapes[0].GetAnchor(), anchor1);
Assert.AreEqual(shapes[1].GetAnchor(), anchor2);
Assert.AreEqual(shapes[2].GetAnchor(), anchor3);
checkRewrite(wb2);
wb2.Close();
}
public static MemoryStream Export(DataSet dataSet, IList <Image> jpgImagesList, Dictionary <bool, string> boolKeyValue, string fileExt)
{
DataSet ds = dataSet.Copy();
IWorkbook workbook;
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
switch (fileExt)
{
case ".xlsx":
workbook = new XSSFWorkbook();
break;
case ".xls":
workbook = new HSSFWorkbook();
break;
default:
workbook = null;
break;
}
if (workbook == null)
{
return(null);
}
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int tableIndex = -1;
for (int i = 0; i < 32; i++)
{
sheet.SetColumnWidth(i, 20 * 256);
}
switch (fileExt)
{
case ".xlsx":
{
if (jpgImagesList != null)
{
XSSFDrawing patriarch = sheet.CreateDrawingPatriarch() as XSSFDrawing;
foreach (Image image in jpgImagesList)
{
byte[] bytes = ConvertImage(image);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor {
Row1 = rowIndex
};
XSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx) as XSSFPicture;
if (pict != null)
{
pict.Resize();
}
rowIndex = anchor.Row2 + 2;
}
}
}
break;
case ".xls":
{
if (jpgImagesList != null)
{
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
foreach (Image image in jpgImagesList)
{
byte[] bytes = ConvertImage(image);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
HSSFClientAnchor anchor = new HSSFClientAnchor {
Row1 = rowIndex
};
HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx) as HSSFPicture;
if (pict != null)
{
pict.Resize();
}
rowIndex = anchor.Row2 + 2;
}
}
}
break;
}
foreach (DataTable dtSource in ds.Tables)
{
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex >= 60000)
{
sheet = workbook.CreateSheet();
rowIndex = 0;
}
if (rowIndex == 0 || tableIndex != ds.Tables.IndexOf(dtSource))
{
tableIndex = ds.Tables.IndexOf(dtSource);
//#region 表头及样式
//{
// if (dtSource.TableName.IndexOf("Table", StringComparison.Ordinal) != 0)
// {
// IRow headerRow = sheet.CreateRow(rowIndex);
// headerRow.HeightInPoints = dtSource.TableName.Length != new Regex(" ").Replace(dtSource.TableName, "\n", 1).Length ? 25 : 50;
// headerRow.CreateCell(0).SetCellValue(new Regex(" ").Replace(dtSource.TableName, "\n", 1));
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// //font.Color = HSSFColor.WHITE.index;
// headStyle.SetFont(font);
// headStyle.WrapText = true;
// //headStyle.FillForegroundColor = GetXLColour(workbook, AppConfig.ZhongTaiLightRed);
// //headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, dtSource.Columns.Count - 1 < 0 ? 0 : dtSource.Columns.Count - 1));
// }
//}
//#endregion
rowIndex = -1;
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex + 1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
//font.Color = HSSFColor.WHITE.index;
headStyle.SetFont(font);
headStyle.WrapText = true;
//headStyle.FillForegroundColor = GetXLColour(workbook, AppConfig.ZhongTaiLightRed);
//headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(new Regex(" ").Replace(column.ColumnName, "\n", 1));
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
}
#endregion
rowIndex += 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
if (boolKeyValue != null)
{
string value;
boolKeyValue.TryGetValue(boolV, out value);
newCell.SetCellValue(value);
}
else
{
newCell.SetCellValue(boolV ? "√" : "×");
}
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
rowIndex += 2;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
//sheet.Dispose();
return(ms);
}
}
/// <summary>
/// 集合导出Excel
/// </summary>
/// <param name="list">集合</param>
/// <param name="columnNames">列名转换</param>
/// <param name="dicOnly">部分转换</param>
/// <returns></returns>
public static byte[] NpoiListToExcel(List <T> list, string sheetName = "Sheet1")
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
var headRow = sheet.CreateRow(0);
PropertyInfo[] props = typeof(T).GetProperties();
for (var i = 0; i < props.Length; ++i)
{
Object obj = props[i].GetCustomAttribute(typeof(ExcelAttribute));
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
headRow.CreateCell(i).SetCellValue(head.Header);
}
else
{
headRow.CreateCell(i).SetCellValue(props[i].Name);
}
}
for (var i = 0; i < list.Count; ++i)
{
var row = sheet.CreateRow(i + 1);
for (var j = 0; j < props.Length; ++j)
{
Object obj = props[j].GetCustomAttribute(typeof(ExcelAttribute));
var key = props[j];
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
if (head.Picture)
{
string url = props[j].GetValue(list[i]).ToString().Split(",")[0];
if (!string.IsNullOrWhiteSpace(url))
{
row.Height = 80 * 20;
WebClient temp = new WebClient();
byte[] bytes = temp.DownloadData(url);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2);
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
bytes = null;
}
continue;
}
else
{
var drValue = props[j].GetValue(list[i]) == null ? "" : props[j].GetValue(list[i]).ToString();
var type = props[j].PropertyType;
if (head.Obj != null)
{
type = head.Obj.GetType();
}
if (type == typeof(Int32) || type == typeof(Int32?))
{
int intV = 0;
int.TryParse(drValue, out intV);
row.CreateCell(j).SetCellValue(intV);
}
else if (type == typeof(System.Decimal) || type == typeof(System.Decimal?) ||
type == typeof(System.Double) || type == typeof(System.Double?))
{
double doubV = 0;
double.TryParse(drValue, out doubV);
if (string.IsNullOrWhiteSpace(drValue))
{
row.CreateCell(j).SetCellValue("");
}
else
{
row.CreateCell(j).SetCellValue(doubV);
}
}
else if (type == typeof(System.Byte) || type == typeof(System.Byte?))
{
Byte doubV = 0;
Byte.TryParse(drValue, out doubV);
row.CreateCell(j).SetCellValue(doubV);
}
else if (type == typeof(System.Boolean) || type == typeof(System.Boolean?))
{
Boolean doubV = false;
Boolean.TryParse(drValue, out doubV);
row.CreateCell(j).SetCellValue(doubV);
}
else
{
row.CreateCell(j).SetCellValue(drValue);
}
}
}
}
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= props.Length; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length + 1;
}
}
if (columnWidth > 40)
{
columnWidth = 40;
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
workbook.Close();
stream.Close();
stream.Dispose();
return(buf);
}
/// <summary>
/// 简单实现图片替换
/// </summary>
/// <param name="fileFullPath"></param>
/// <param name="replacements"></param>
public void ReplacePlaceholders(string fileFullPath, List <EasyNPOI.Models.Excel.Replacement> replacements)
{
if (string.IsNullOrEmpty(fileFullPath))
{
throw new ArgumentNullException();
}
var fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read);
var extName = Path.GetExtension(fileFullPath);
IWorkbook wb;
if (extName == ".xls")
{
wb = new HSSFWorkbook(fs);
}
else if (extName == ".xlsx")
{
wb = new XSSFWorkbook(fs);
}
else
{
wb = null;
}
if (wb == null)
{
throw new ArgumentNullException();
}
var snum = wb.NumberOfSheets;
var reSave = false;
for (int i = 0; i < snum; i++)
{
//if (wb.IsSheetHidden(i))
//{
// continue;
//}
var sheet = wb.GetSheetAt(i);
IRow row = sheet.GetRow(sheet.LastRowNum);
if (row == null)
{
continue;
}
var cells = row.Cells.Where(p => p.CellType == CellType.String).ToList();
foreach (var x in cells)
{
var colNum = x.ColumnIndex;
var rowNum = x.RowIndex;
var cellText = x.StringCellValue;
if (string.IsNullOrEmpty(cellText))
{
continue;
}
foreach (var replace in replacements)
{
if (cellText.Contains(replace.Placeholder))
{
//获取指定图片的字节流
//byte[] bytes = new byte[pictureData.Length];
//pictureData.Read(bytes, 0, bytes.Length);
byte[] bytes = System.IO.File.ReadAllBytes(replace.PictureUrl);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = wb.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
if (extName == ".xls")
{
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, colNum, rowNum, colNum + 1, rowNum + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
reSave = true;
}
else if (extName == ".xlsx")
{
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：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),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(100, 100, 0, 0, colNum, rowNum, colNum + 1, rowNum + 1);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
reSave = true;
}
cellText = cellText.Replace(replace.Placeholder, "");
x.SetCellValue(cellText);
}
}
}
}
if (reSave)
{
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(fileFullPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
wb.Write(file);
//关闭工作簿
wb.Close();
}
}
/// <summary>
/// 通过模板生成EXCEL
/// </summary>
/// <param name="workbook"></param>
/// <param name="dataList">sheet集合</param>
/// <param name="temName">模板名称</param>
/// <returns></returns>
public static byte[] writeExcelToFile(XSSFWorkbook workbook, List <ExcelSheetModel> dataList, String temName)
{
// 列宽一个像素的固定值
//decimal pixCellConst = 31.94888178913738m;
// 行宽一个像素的固定值
//decimal pixRowConst = 15.15151515151515m;
String location = null;
XSSFRow row = null;
foreach (ExcelSheetModel dataSheet in dataList)
{
if (!string.IsNullOrEmpty(dataSheet.sheetType))
{
}
else
{
#region 自定义模式
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(dataSheet.sheetName);
// 自动更新公式
sheet.ForceFormulaRecalculation = true;
foreach (ExcelCellModel dataCell in dataSheet.dataList)
{
if (!string.IsNullOrEmpty(dataCell.location))
{
location = dataCell.location.ToUpper();
int dataNum = 0;
int dataRow = 0;
string rowStr = "";
string columnStr = "";
for (int i = 0; i < location.Length; i++)
{
var locationArr = location.ToCharArray();
if (locationArr[i] >= 48 && locationArr[i] <= 57)
{
columnStr += locationArr[i];
}
if (locationArr[i] >= 65 && locationArr[i] <= 90)
{
rowStr += locationArr[i];
}
}
for (int i = 0; i < rowStr.Length; i++)
{
var rowStrArr = rowStr.ToCharArray();
char ch = rowStrArr[rowStr.Length - i - 1];
dataNum = (int)(ch - 'A' + 1);
dataNum *= int.Parse(Math.Pow(26, i).ToString());
dataRow += dataNum;
}
dataCell.rowNum = int.Parse(columnStr) - 1;
dataCell.cellNum = dataRow - 1;
}
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->Strat");
XSSFCellStyle setBorder = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)workbook.CreateFont();
XSSFColor fontColor = null;
XSSFColor backColor = null;
if (row == null || row.RowNum != dataCell.rowNum)
{
row = (XSSFRow)sheet.GetRow(dataCell.rowNum);
}
XSSFCell cell = (XSSFCell)row.CreateCell(dataCell.cellNum);
// check合并单元格
if (dataCell.regionCell != null)
{
if (dataCell.regionCell.Length == 2)
{
if (dataCell.rowNum > dataCell.regionCell[0])
{
//logger.Debug("RegionCella结束行小于当前行");
}
else if (dataCell.cellNum > dataCell.regionCell[1])
{
//logger.Debug("RegionCella结束列小于当前列");
}
else
{
sheet.AddMergedRegion(
new CellRangeAddress(dataCell.rowNum, dataCell.regionCell[0],
dataCell.cellNum, dataCell.regionCell[1]));
}
}
else
{
//logger.Debug("RegionCell参数异常");
}
}
// check列宽
if (dataCell.cellWidth != 0)
{
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* int.Parse(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString())).ToString()));
* //*/
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* Convert.ToInt32(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString()))));
* //*/
sheet.SetColumnWidth(dataCell.cellNum, dataCell.cellWidth);
}
// check行高
if (dataCell.cellHeight != 0)
{
row.Height = dataCell.cellHeight;
}
// check字体颜色
if (dataCell.cellColor != null)
{
if (dataCell.cellColor.Length == 3)
{
if (((dataCell.cellColor[0] >= 0) || (dataCell.cellColor[0] <= 255)) &&
((dataCell.cellColor[1] >= 0) || (dataCell.cellColor[1] <= 255)) &&
((dataCell.cellColor[2] >= 0) || (dataCell.cellColor[2] <= 255)))
{
fontColor = new XSSFColor(Color.FromArgb(dataCell.cellColor[0],
dataCell.cellColor[1], dataCell.cellColor[2]));
font.SetColor(fontColor);
}
}
}
// check背景色
if (dataCell.backColor != null)
{
if (dataCell.backColor.Length == 3)
{
if (((dataCell.backColor[0] >= 0) || (dataCell.backColor[0] <= 255)) &&
((dataCell.backColor[1] >= 0) || (dataCell.backColor[1] <= 255)) &&
((dataCell.backColor[2] >= 0) || (dataCell.backColor[2] <= 255)))
{
backColor = new XSSFColor(Color.FromArgb(dataCell.backColor[0],
dataCell.backColor[1], dataCell.backColor[2]));
setBorder.FillPattern = FillPattern.SolidForeground;
setBorder.SetFillForegroundColor(backColor);
}
}
}
// check边框
if (dataCell.borderLine != null)
{
if (dataCell.borderLine.Length == 4)
{
setBorder.BorderTop = dataCell.borderLine[0];
setBorder.BorderBottom = dataCell.borderLine[1];
setBorder.BorderLeft = dataCell.borderLine[2];
setBorder.BorderRight = dataCell.borderLine[3];
}
}
// check字体
if (!string.IsNullOrEmpty(dataCell.fontName))
{
font.FontName = dataCell.fontName;
}
else
{
font.FontName = "宋体";
}
// check字体大小
if (dataCell.fontSize != 0)
{
font.FontHeightInPoints = dataCell.fontSize;
}
else
{
font.FontHeightInPoints = (short)10;
}
// 字体是否加粗
if (dataCell.Boldweight)
{
font.IsBold = true;
}
// 是否自动换行
if (dataCell.wrapText)
{
setBorder.WrapText = true;
}
// 单元格的值
if (!string.IsNullOrEmpty(dataCell.cellValue))
{
cell.SetCellValue(dataCell.cellValue);
}
else
{
cell.SetCellValue("");
}
// 水平类型
setBorder.Alignment = dataCell.horizontalAlignment;
// 垂直类型
setBorder.VerticalAlignment = dataCell.verticalAlignment;
// 图片
if (dataCell.excelPictureModel != null)
{
int col2 = 0;
int row2 = 0;
byte[] arr = null;
if (dataCell.excelPictureModel.CodeType == PrintStructFlag.QRCODE)
{//二维码
MemoryStream pictureIS = QRCodeUtil.GetQRCode(dataCell.excelPictureModel.qrCode);
arr = new byte[pictureIS.Length];
pictureIS.Position = 0;
pictureIS.Read(arr, 0, (int)pictureIS.Length);
pictureIS.Close();
col2 = dataCell.excelPictureModel.endColNum + 1;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
else if (dataCell.excelPictureModel.CodeType == PrintStructFlag.BARCODE)
{ //一维码
BarCodeClass bc = new BarCodeClass();
Image image = bc.ZXCreateBarCode(dataCell.excelPictureModel.qrCode);
ImageConverter imgconv = new ImageConverter();
arr = (byte[])imgconv.ConvertTo(image, typeof(byte[]));
col2 = dataCell.excelPictureModel.endColNum + 4;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
int pIndex = workbook.AddPicture(arr, NPOI.SS.UserModel.PictureType.JPEG);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, dataCell.cellNum,
dataCell.rowNum,
col2,
row2);
patriarch.CreatePicture(anchor, pIndex);
}
setBorder.SetFont(font);
cell.CellStyle = setBorder;
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->End");
}
#endregion
}
}
string filePath = System.Threading.Thread.GetDomain().BaseDirectory + "\\";
var new_tempFileName = "to_" + temName + ".xlsx";
FileStream files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.Create);
workbook.Write(files);
files.Close();
files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.OpenOrCreate);
byte[] bytes = new byte[files.Length];
files.Read(bytes, 0, bytes.Length);
files.Close();
workbook.Close();
return(bytes);
}
///// <summary>
///// 清除时间
///// </summary>
///// <param name="sender"></param>
///// <param name="e"></param>
//private void ClearDatePickerTime(object sender, MouseButtonEventArgs e)
//{
// DatePicker dp = sender as DatePicker;
// dp.Text = "";
//}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void ExportExcelFile_Click(object sender, RoutedEventArgs e)
{
try
{
string saveExcelPath = ConfigurationManager.AppSettings["Excel保存地址"];
if (string.IsNullOrEmpty(saveExcelPath))
{
saveExcelPath = System.Windows.Forms.Application.StartupPath + @"\比对记录导出";
}
else
{
saveExcelPath += @"\比对记录导出";
}
await Task.Run(
() =>
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
XSSFCellStyle style2 = (XSSFCellStyle)workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style2.BorderBottom = BorderStyle.Thin;
style2.BorderLeft = BorderStyle.Thin;
style2.BorderRight = BorderStyle.Thin;
style2.BorderTop = BorderStyle.Thin;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
style2.SetFont(font);
XSSFCellStyle style0 = (XSSFCellStyle)workbook.CreateCellStyle();
style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style0.BorderBottom = BorderStyle.Thin;
style0.BorderLeft = BorderStyle.Thin;
style0.BorderRight = BorderStyle.Thin;
style0.BorderTop = BorderStyle.Thin;
IDataFormat dataformat = workbook.CreateDataFormat();
style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 h:mm:ss 上午/下午");
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("比对记录");
int row = 0;
List <MyCmpFaceLogWidthImgModel> allCmpInfo = new List <MyCmpFaceLogWidthImgModel>();
allCmpInfo = GetCmpFaceLogByPage(1);
foreach (var item in allCmpInfo)
{
if (row == 0)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue("序号");
sheet.GetRow(row).CreateCell(1).SetCellValue("抓拍通道");
sheet.GetRow(row).CreateCell(2).SetCellValue("抓拍时间");
sheet.GetRow(row).CreateCell(3).SetCellValue("模版姓名");
sheet.GetRow(row).CreateCell(4).SetCellValue("注册类型");
sheet.GetRow(row).CreateCell(5).SetCellValue("相似度");
sheet.GetRow(row).CreateCell(6).SetCellValue("抓拍照片");
sheet.GetRow(row).CreateCell(7).SetCellValue("模版照片");
sheet.GetRow(row).GetCell(0).CellStyle = style2;
sheet.GetRow(row).GetCell(1).CellStyle = style2;
sheet.GetRow(row).GetCell(2).CellStyle = style2;
sheet.GetRow(row).GetCell(3).CellStyle = style2;
sheet.GetRow(row).GetCell(4).CellStyle = style2;
sheet.GetRow(row).GetCell(5).CellStyle = style2;
sheet.GetRow(row).GetCell(6).CellStyle = style2;
sheet.GetRow(row).GetCell(7).CellStyle = style2;
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
row++;
}
sheet.CreateRow(row).CreateCell(0).SetCellValue(row);
sheet.GetRow(row).Height = 100 * 40;
sheet.GetRow(row).CreateCell(1).SetCellValue(item.channelName);
sheet.GetRow(row).CreateCell(2).SetCellValue(item.time);
sheet.GetRow(row).CreateCell(3).SetCellValue(item.name);
sheet.GetRow(row).CreateCell(4).SetCellValue(item.type);
sheet.GetRow(row).CreateCell(5).SetCellValue(item.score);
sheet.GetRow(row).CreateCell(6).SetCellValue("");
sheet.GetRow(row).CreateCell(7).SetCellValue("");
sheet.GetRow(row).GetCell(0).CellStyle = style;
sheet.GetRow(row).GetCell(1).CellStyle = style;
sheet.GetRow(row).GetCell(2).CellStyle = style0;
sheet.GetRow(row).GetCell(3).CellStyle = style;
sheet.GetRow(row).GetCell(4).CellStyle = style;
sheet.GetRow(row).GetCell(5).CellStyle = style;
sheet.GetRow(row).GetCell(6).CellStyle = style;
sheet.GetRow(row).GetCell(7).CellStyle = style;
if (true)
{
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
List <byte[]> capImage = new List <byte[]>();
capImage = thirft.QueryCmpLogImageH(item.ID, exportCurrDay);
if (capImage.Count > 0)
{
byte[] bytes = capImage[0];
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 6, row, 7, row + 1);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
var faceObject = thirft.QueryFaceObj(item.tcUuid);
if (faceObject.Count > 0 && faceObject[0].Tmplate.Count > 0)
{
byte[] bytes2 = faceObject[0].Tmplate[0].Img;
int pictureIdx2 = workbook.AddPicture(bytes2, PictureType.JPEG);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 7, row, 8, row + 1);
XSSFPicture pict2 = (XSSFPicture)patriarch.CreatePicture(anchor2, pictureIdx2);
}
}
row++;
Thread.Sleep(1);
}
FileStream fs = new FileStream(saveExcelPath + ".xlsx", FileMode.Create, FileAccess.Write);
workbook.Write(fs);
workbook = null;
});
MB_MODULES.Views.MyMessage.showYes("导出完成!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}