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);
}
public void TestRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
IList pictures = wb.GetAllPictures();
//wb.GetAllPictures() should return the same instance across multiple calls
Assert.AreSame(pictures, wb.GetAllPictures());
Assert.AreEqual(5, pictures.Count);
String[] ext = { "jpeg", "emf", "png", "emf", "wmf" };
String[] mimetype = { "image/jpeg", "image/x-emf", "image/png", "image/x-emf", "image/x-wmf" };
for (int i = 0; i < pictures.Count; i++)
{
Assert.AreEqual(ext[i], ((XSSFPictureData)pictures[i]).SuggestFileExtension());
Assert.AreEqual(mimetype[i], ((XSSFPictureData)pictures[i]).MimeType);
}
int num = pictures.Count;
byte[] pictureData = { 0xA, 0xB, 0XC, 0xD, 0xE, 0xF };
int idx = wb.AddPicture(pictureData, PictureType.JPEG);
Assert.AreEqual(num + 1, pictures.Count);
//idx is 0-based index in the #pictures array
Assert.AreEqual(pictures.Count - 1, idx);
XSSFPictureData pict = (XSSFPictureData)pictures[idx];
Assert.AreEqual("jpeg", pict.SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pictureData, pict.Data));
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
//add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
ICreationHelper helper = wb.GetCreationHelper();
// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();
// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
FileStream sw = File.Create("../../data/image.xlsx");
wb.Write(sw);
sw.Close();
}
public void TestReadAnchors()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.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 = wb.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);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.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);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
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>
/// 向sheet插入图片
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="sheet">工作表</param>
/// <param name="filePath">文件路径</param>
/// <param name="row">第几行</param>
/// <param name="col">第几列</param>
/// <param name="suffix">后缀</param>
private static void AddPicture(XSSFWorkbook workbook, ISheet sheet, string filePath, int row, int col, string suffix)
{
try
{
if (!string.IsNullOrEmpty(filePath))
{
//转为本地路径
filePath = HttpContext.Current.Server.MapPath(filePath);
if (File.Exists(filePath))
{
var bytes = File.ReadAllBytes(filePath);
if (bytes.Length > 0)
{
var imagedId = 0;
var pictureType = new PictureType();
if (suffix.Equals("png", StringComparison.OrdinalIgnoreCase))
{
pictureType = PictureType.PNG;
}
else if (suffix.Equals("jpg", StringComparison.OrdinalIgnoreCase))
{
pictureType = PictureType.JPEG;
}
else
{
pictureType = PictureType.JPEG;
}
var patriarch = sheet.CreateDrawingPatriarch();
imagedId = workbook.AddPicture(bytes, pictureType);
//处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，第三个参数为宽，第四个参数为高
var anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
//dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
//dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
//dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
//dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
anchor.AnchorType = AnchorType.MoveDontResize;
//anchor.AnchorType = 3;
//load the picture and get the picture index in the workbook
var picture = (XSSFPicture)patriarch.CreatePicture(anchor, imagedId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set. 这句话一定不要，这是用图片原始大小来显示
//picture.Resize(0.5);
picture.LineStyle = LineStyle.DashDotGel;
}
}
}
}
catch (Exception ex)
{
LogUtil.WriteException(ex);
//此处采用跳过的方式
//throw ex;
}
}
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>
/// .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);
}
}
private static void InsertImage(XSSFWorkbook workbook, ISheet sheet, byte[] data, int row, int column)
{
try
{
int picInd = workbook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1)
{
AnchorType = AnchorType.MoveAndResize
};
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, picInd);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
}
catch
{
//ignore
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">数据</param>
/// <param name="headDict">头部信息</param>
/// <param name="imgColumnList">图片列</param>
/// <param name="sheetName">Sheet名</param>
/// <param name="showSrNo">是否要加上序号</param>
/// <returns></returns>
public static byte[] ExportExcel <T>(List <T> data, Dictionary <string, string> headDict, List <string> imgColumnList, string sheetName = "", bool showSrNo = false)
{
if (imgColumnList == null)
{
imgColumnList = new List <string>();
}
DataTable dt = ListToDataTable <T>(data);
byte[] result = null;
List <string> keyList = new List <string>();
if (showSrNo)
{
keyList.Add("RowNum");
dt.Columns.Add("RowNum");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["RowNum"] = i + 1;
}
}
//通过键的集合取
foreach (string key in headDict.Keys)
{
keyList.Add(key);
}
IWorkbook workbook = new XSSFWorkbook();
//设置宽度
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
style.Alignment = HorizontalAlignment.Center; //水平对齐;
if (showSrNo)
{
headDict.Add("RowNum", "序号");
}
ISheet sheet = sheetName.IsNullOrEmpty() ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < keyList.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(headDict[keyList[i]]);
cell.CellStyle = style;
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < keyList.Count; j++)
{
if (imgColumnList.Contains(keyList[j]))
{
//插入图片
byte[] bytes = HttpMethods.GetImage(dt.Rows[i][keyList[j]].ToString());
if (bytes != null)
{
ICell cell = row1.CreateCell(j);
cell.CellStyle = style;
try
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 100, j, i + 1, j + 1, i + 2);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
catch
{
cell.SetCellValue(dt.Rows[i][keyList[j]].ToString());
}
}
}
else
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][keyList[j]].ToString());
cell.CellStyle = style;
}
}
}
//自适应列宽
for (int i = 0; i < keyList.Count; i++)
{
sheet.AutoSizeColumn(i, true);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
result = ms.GetBuffer();
ms.Close();
};
return(result);
}
/// <summary>
/// 将datatable导出为excel
/// 图片默认显示在excel 第二行最后一列
/// </summary>
/// <param name="table">数据源</param>
/// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
/// <param name="sheetName">工作簿名称</param>
/// <param name="picBytes">导出图片字节流</param>
/// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
/// <returns></returns>
public static MemoryStream ExportToExcel2007(DataTable table, List <Tuple <string, string, int> > excelInfo, string sheetName, byte[] picBytes, List <CellRangeAddress> mergedRegion)
{
MemoryStream ms = new MemoryStream();
try
{
using (table)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
for (int i = 0; i < excelInfo.Count; i++)
{
sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
}
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelInfo.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
}
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < excelInfo.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
}
rowIndex++;
}
//合并单元格
if (mergedRegion != null && mergedRegion.Count > 0)
{
foreach (CellRangeAddress cellRangeAddress in mergedRegion)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(cellRangeAddress);
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//将新的样式赋给单元格
var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
cell.CellStyle = style;
}
}
//插入图片
if (picBytes != null && picBytes.Length > 0)
{
var row1 = 2;
var col1 = excelInfo.Count + 1;
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = workbook.AddPicture(picBytes, PictureType.PNG); //添加图片
/* Create the drawing container */
IDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
/* Create an anchor point */
IClientAnchor anchor = new XSSFClientAnchor(1, 1, 0, 0, col1, row1, col1 + 1, row1 + 1);
/* Invoke createPicture and pass the anchor point and ID */
IPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
/* Call resize method, which resizes the image */
picture.Resize();
picBytes = null;
}
workbook.Write(ms);
workbook.Close();
}
}
catch (Exception ex)
{
ms = null;
}
return(ms);
}
///// <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);
}
}
public int AddPicture(byte[] pictureData, PictureType format)
{
return(XssfWorkbook.AddPicture(pictureData, format));
}
public async Task <ActionResult> UploadScoreAjaxDown(string disCode, string startTime, string endTime, string status, string pid, string disname,
string name, string visitTypeName, string visitDateTime, string visitType, string batch)
{
try
{
string result = await CommonHelper.GetHttpClient().GetStringAsync(CommonHelper.Current.GetAPIBaseUrl + "Tour/GetTaskListByDisIdForExcel/" + disCode + "/" + startTime + "/" + endTime + "/" + status + "/" + pid);
var apiResult = CommonHelper.DecodeString <APIResult>(result);
if (apiResult.ResultCode == ResultType.Success)
{
ExcelResult er = CommonHelper.DecodeString <ExcelResult>(apiResult.Body);
List <ResultExcelDto> list = er.ResultList;
string discode = list.FirstOrDefault().DisCode;
string pDisInfo = list.FirstOrDefault().PId;
string aDisName = string.Empty;
string rDisName = string.Empty;
string eDisName = string.Empty;
if (!string.IsNullOrWhiteSpace(pDisInfo) && pDisInfo.Split(',').Length == 3)
{
aDisName = pDisInfo.Split(',')[0];
rDisName = pDisInfo.Split(',')[1];
eDisName = pDisInfo.Split(',')[2];
}
List <LosePic> lpList = er.LPicList;
if (lpList.Count % 2 == 1)
{
lpList.Add(new LosePic());
}
//未通过体系
List <ResultExcelDto> unlist2 = (from a in list where a.PassYN == "0" select a).ToList();
List <string> tiId = (from a in unlist2 select a.TITitle).Distinct().ToList();
//任务卡得分
var ts = (from a in list select new { TCId = a.TCId, TCTitle = a.TCTitle, TCWeight = a.TCWeight, ItemScore = a.ItemScore, ItemQty = a.ItemQty }).Distinct().ToList();
string totalScore = list.FirstOrDefault().STScore;
var uploads = Path.Combine(_environment.WebRootPath, "Template");
//var newFile = Path.Combine(uploads, list[0].PTitle + "_" + discode + "_" + disname + "_" + DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xlsx");
var reportname = list[0].PTitle + "_" + visitTypeName + "_" + discode + "_" + disname;
var newFile = Path.Combine(uploads, reportname + ".xlsx");
if (!Directory.Exists(uploads))
{
Directory.CreateDirectory(uploads);
}
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
//ISheet sheet1 = workbook.CreateSheet("检核结果");
ISheet sheet1 = workbook.CreateSheet(reportname);
sheet1.FitToPage = true;
sheet1.PrintSetup.FitWidth = 1;
sheet1.PrintSetup.FitHeight = 0;
sheet1.PrintSetup.PaperSize = 9;
sheet1.SetMargin(MarginType.RightMargin, 0);
//sheet1.PrintSetup.NoOrientation = true;
IFont font_b = workbook.CreateFont();
font_b.FontName = "微软雅黑";
font_b.FontHeightInPoints = 10;
IFont font_c = workbook.CreateFont();
font_c.FontName = "微软雅黑";
font_c.FontHeightInPoints = 10;
font_c.Color = HSSFColor.White.Index;
font_c.IsBold = true;
byte[] rgb5 = new byte[3] {
217, 217, 217
};
byte[] rgb6 = new byte[3] {
221, 217, 196
};
byte[] rgb7 = new byte[3] {
32, 55, 100
};
var style1 = (XSSFCellStyle)workbook.CreateCellStyle();
//style1.FillForegroundColor = HSSFColor.Grey25Percent.Index;
byte[] rgb = new byte[3] {
116, 163, 210
};
style1.SetFillForegroundColor(new XSSFColor(rgb));
style1.FillPattern = FillPattern.SolidForeground;
style1.Alignment = HorizontalAlignment.Center;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderBottom = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.SetFont(font_b);
var style_Stand = workbook.CreateCellStyle();
style_Stand.Alignment = HorizontalAlignment.Left;
style_Stand.WrapText = true;
style_Stand.SetFont(font_b);
style_Stand.BorderLeft = BorderStyle.Thin;
style_Stand.BorderBottom = BorderStyle.Thin;
style_Stand.BorderRight = BorderStyle.Thin;
style_Stand.BorderTop = BorderStyle.Thin;
var style_b2 = workbook.CreateCellStyle();
style_b2.WrapText = true;
style_b2.VerticalAlignment = VerticalAlignment.Center;
style_b2.SetFont(font_b);
style_b2.BorderLeft = BorderStyle.Thin;
style_b2.BorderBottom = BorderStyle.Thin;
style_b2.BorderRight = BorderStyle.Thin;
style_b2.BorderTop = BorderStyle.Thin;
var style_b3 = workbook.CreateCellStyle();
style_b3.WrapText = true;
style_b3.VerticalAlignment = VerticalAlignment.Center;
style_b3.SetFont(font_b);
var style2 = workbook.CreateCellStyle();
style2.Alignment = HorizontalAlignment.Left;
style2.SetFont(font_b);
style2.BorderLeft = BorderStyle.Thin;
style2.BorderBottom = BorderStyle.Thin;
style2.BorderRight = BorderStyle.Thin;
style2.BorderTop = BorderStyle.Thin;
var style3 = workbook.CreateCellStyle();
style3.Alignment = HorizontalAlignment.Right;
style3.SetFont(font_b);
var style4 = workbook.CreateCellStyle();
style4.Alignment = HorizontalAlignment.Center;
style4.VerticalAlignment = VerticalAlignment.Center;
style4.SetFont(font_b);
style4.BorderLeft = BorderStyle.Thin;
style4.BorderBottom = BorderStyle.Thin;
style4.BorderRight = BorderStyle.Thin;
style4.BorderTop = BorderStyle.Thin;
var style5 = workbook.CreateCellStyle();
style5.Alignment = HorizontalAlignment.Center;
style5.VerticalAlignment = VerticalAlignment.Center;
style5.SetFont(font_b);
var style6 = workbook.CreateCellStyle();
style6.Alignment = HorizontalAlignment.Center;
style6.VerticalAlignment = VerticalAlignment.Center;
style6.SetFont(font_b);
var style_t = (XSSFCellStyle)workbook.CreateCellStyle();
style_t.Alignment = HorizontalAlignment.Center;
style_t.SetFillForegroundColor(new XSSFColor(rgb));
//style_t.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style_t.FillPattern = FillPattern.SolidForeground;
IFont font_t = workbook.CreateFont();
font_t.FontHeightInPoints = 18;
font_t.FontName = "微软雅黑";
style_t.SetFont(font_t);
//标题
IRow row_t = sheet1.CreateRow(0);
ICell cell_t = row_t.CreateCell(0);
cell_t.SetCellValue("经销商报告");
CellRangeAddress region_t = new CellRangeAddress(0, 0, 0, 6);
cell_t.CellStyle = style_t;
sheet1.AddMergedRegion(region_t);
IRow row = sheet1.CreateRow(1);
ICell cell = row.CreateCell(0);
row.CreateCell(1).CellStyle = style2;
cell.SetCellValue("经销商代码:" + discode);
CellRangeAddress region = new CellRangeAddress(1, 1, 0, 1);
cell.CellStyle = style2;
sheet1.AddMergedRegion(region);
ICell cell_11 = row.CreateCell(2);
row.CreateCell(3).CellStyle = style2;
row.CreateCell(4).CellStyle = style2;
row.CreateCell(5).CellStyle = style2;
row.CreateCell(6).CellStyle = style2;
cell_11.SetCellValue("经销商名称：" + disname);
CellRangeAddress region_1 = new CellRangeAddress(1, 1, 2, 6);
cell_11.CellStyle = style2;
sheet1.AddMergedRegion(region_1);
IRow row1 = sheet1.CreateRow(2);
ICell cel2 = row1.CreateCell(0);
row1.CreateCell(1).CellStyle = style2;
cel2.SetCellValue("所属小区：" + aDisName);
cel2.CellStyle = style2;
CellRangeAddress region2 = new CellRangeAddress(2, 2, 0, 1);
sheet1.AddMergedRegion(region2);
ICell cel2_1 = row1.CreateCell(2);
row1.CreateCell(3).CellStyle = style2;
row1.CreateCell(4).CellStyle = style2;
cel2_1.SetCellValue(rDisName == "-" ? "所属大区：" + eDisName : "所属区域：" + rDisName);
cel2_1.CellStyle = style2;
CellRangeAddress region2_1 = new CellRangeAddress(2, 2, 2, 4);
sheet1.AddMergedRegion(region2_1);
ICell cel2_2 = row1.CreateCell(5);
row1.CreateCell(6).CellStyle = style2;
cel2_2.SetCellValue(rDisName == "-" ? "" : (eDisName == "-" ? "" : "所属大区：" + eDisName));
cel2_2.CellStyle = style2;
CellRangeAddress region2_2 = new CellRangeAddress(2, 2, 5, 6);
sheet1.AddMergedRegion(region2_2);
IRow row0 = sheet1.CreateRow(3);
ICell cel0 = row0.CreateCell(0);
row0.CreateCell(1).CellStyle = style2;
cel0.SetCellValue("检核类型：" + visitTypeName);
cel0.CellStyle = style2;
CellRangeAddress region3 = new CellRangeAddress(3, 3, 0, 1);
sheet1.AddMergedRegion(region3);
ICell cel0_1 = row0.CreateCell(2);
row0.CreateCell(3).CellStyle = style2;
row0.CreateCell(4).CellStyle = style2;
cel0_1.SetCellValue("巡检月份：" + (list.Count == 0 ? "" : list[0].PTitle));
cel0_1.CellStyle = style2;
CellRangeAddress region3_1 = new CellRangeAddress(3, 3, 2, 4);
sheet1.AddMergedRegion(region3_1);
ICell cel0_2 = row0.CreateCell(5);
row0.CreateCell(6).CellStyle = style2;
cel0_2.SetCellValue("检核时间：" + visitDateTime);
cel0_2.CellStyle = style2;
CellRangeAddress region3_2 = new CellRangeAddress(3, 3, 5, 6);
sheet1.AddMergedRegion(region3_2);
if (visitType == "D" || visitType == "E" || visitType == "F")
{
//DMS 和 卖车宝
string plansPosition = await CommonHelper.GetHttpClient().GetStringAsync(string.Format("{0}/Tour/GetPlansPosition?batch={1}&disId={2}", CommonHelper.Current.GetAPIBaseUrl, batch, disCode));
APIResult planAPIResult = CommonHelper.DecodeString <APIResult>(plansPosition);
List <Dictionary <string, object> > dicList = CommonHelper.DecodeString <List <Dictionary <string, object> > >(planAPIResult.Body);
if (dicList != null && dicList.Count > 0)
{
Dictionary <string, object> dic = dicList[0];
IRow row4 = sheet1.CreateRow(4);
ICell[] cells = new ICell[7];
for (int i = 0; i < 7; i++)
{
cells[i] = row4.CreateCell(i);
cells[i].CellStyle = style2;
}
string txtManager = "销售经理：" + dic["SalesManager"];
string txtConsultant = "销售顾问：" + dic["SalesConsultant"];
string txtInside = "销售内勤：" + dic["SalesInside"];
if (visitType == "F")
{
txtManager = "客户经理：" + dic["SalesManager"];
txtConsultant = "零件经理：" + dic["SalesConsultant"];
txtInside = "保修专员：" + dic["SalesInside"];
}
if (visitType == "D" || visitType == "F")
{
cells[0].SetCellValue(txtManager);
cells[2].SetCellValue(txtConsultant);
cells[5].SetCellValue(txtInside);
//合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 0, 1));
sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 2, 4));
sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 5, 6));
}
else
{
cells[0].SetCellValue(txtManager);
cells[3].SetCellValue(txtConsultant);
//合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 0, 2));
sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 3, 6));
}
}
}
var style_sub = (XSSFCellStyle)workbook.CreateCellStyle();
style_sub.Alignment = HorizontalAlignment.Center;
style_sub.SetFillForegroundColor(new XSSFColor(rgb));
//style_sub.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style_sub.FillPattern = FillPattern.SolidForeground;
style_sub.SetFont(font_b);
var style_5 = (XSSFCellStyle)workbook.CreateCellStyle();
style_5.Alignment = HorizontalAlignment.Left;
style_5.SetFillForegroundColor(new XSSFColor(rgb5));
style_5.FillPattern = FillPattern.SolidForeground;
style_5.BorderLeft = BorderStyle.Thin;
style_5.BorderBottom = BorderStyle.Thin;
style_5.BorderRight = BorderStyle.Thin;
style_5.BorderTop = BorderStyle.Thin;
style_5.SetFont(font_b);
var style_6 = (XSSFCellStyle)workbook.CreateCellStyle();
style_6.Alignment = HorizontalAlignment.Center;
style_6.SetFillForegroundColor(new XSSFColor(rgb6));
style_6.FillPattern = FillPattern.SolidForeground;
style_6.BorderLeft = BorderStyle.Thin;
style_6.BorderBottom = BorderStyle.Thin;
style_6.BorderRight = BorderStyle.Thin;
style_6.BorderTop = BorderStyle.Thin;
style_6.SetFont(font_b);
var style_7 = (XSSFCellStyle)workbook.CreateCellStyle();
style_7.Alignment = HorizontalAlignment.Center;
style_7.SetFillForegroundColor(new XSSFColor(rgb7));
style_7.FillPattern = FillPattern.SolidForeground;
style_7.BorderLeft = BorderStyle.Thin;
style_7.BorderBottom = BorderStyle.Thin;
style_7.BorderRight = BorderStyle.Thin;
style_7.BorderTop = BorderStyle.Thin;
style_7.SetFont(font_c);
sheet1.CreateRow(5);
IRow row_score = sheet1.CreateRow(6);
ICell cell_score = row_score.CreateCell(0);
cell_score.SetCellValue("检核结果汇总");
CellRangeAddress region_score = new CellRangeAddress(6, 6, 0, 4);
cell_score.CellStyle = style_sub;
sheet1.AddMergedRegion(region_score);
int tCount = 7;
int sCount = tCount;
IRow rows = sheet1.CreateRow(sCount);
//任务卡名称
ICell cell_s1 = rows.CreateCell(0);
cell_s1.SetCellValue("模块");
cell_s1.CellStyle = style1;
ICell cell_s2 = rows.CreateCell(1);
cell_s2.SetCellValue("");
cell_s2.CellStyle = style1;
CellRangeAddress region_s1 = new CellRangeAddress(sCount, sCount, 0, 1);
sheet1.AddMergedRegion(region_s1);
//指标数量
ICell cell_s3 = rows.CreateCell(2);
cell_s3.SetCellValue("指标数量");
cell_s3.CellStyle = style1;
CellRangeAddress region_s2 = new CellRangeAddress(sCount, sCount, 2, 2);
sheet1.AddMergedRegion(region_s2);
//权重
ICell cell_s4 = rows.CreateCell(3);
cell_s4.SetCellValue("权重");
cell_s4.CellStyle = style1;
CellRangeAddress region_s3 = new CellRangeAddress(sCount, sCount, 3, 3);
sheet1.AddMergedRegion(region_s3);
//得分
ICell cell_s5 = rows.CreateCell(4);
cell_s5.SetCellValue("得分");
cell_s5.CellStyle = style1;
CellRangeAddress region_s4 = new CellRangeAddress(sCount, sCount, 4, 4);
sheet1.AddMergedRegion(region_s4);
sCount++;
if (ts != null && ts.Count > 0)
{
for (int i = 0; i < ts.Count; i++)
{
IRow row_un = sheet1.CreateRow(sCount + i);
ICell cell_0 = row_un.CreateCell(0);
cell_0.SetCellValue(ts[i].TCTitle);
cell_0.CellStyle = style_5;
ICell cell_1 = row_un.CreateCell(1);
cell_1.SetCellValue(ts[i].TCTitle);
cell_1.CellStyle = style_5;
CellRangeAddress r_s1 = new CellRangeAddress(sCount + i, sCount + i, 0, 1);
sheet1.AddMergedRegion(r_s1);
ICell cell_4 = row_un.CreateCell(2);
cell_4.SetCellValue(ts[i].ItemQty);
cell_4.CellStyle = style_6;
CellRangeAddress r_s2 = new CellRangeAddress(sCount + i, sCount + i, 2, 2);
sheet1.AddMergedRegion(r_s2);
ICell cell_5 = row_un.CreateCell(3);
decimal rWeight = 0;
decimal.TryParse(ts[i].TCWeight, out rWeight);
cell_5.SetCellValue(Math.Round(rWeight, 2) * 100 + "%");
cell_5.CellStyle = style_6;
CellRangeAddress r_s3 = new CellRangeAddress(sCount + i, sCount + i, 3, 3);
sheet1.AddMergedRegion(r_s3);
ICell cell_6 = row_un.CreateCell(4);
cell_6.SetCellValue(ts[i].ItemScore);
cell_6.CellStyle = style_6;
CellRangeAddress r_s4 = new CellRangeAddress(sCount + i, sCount + i, 4, 4);
sheet1.AddMergedRegion(r_s4);
}
}
if (ts != null && ts.Count >= 0)
{
sCount = ts.Count + tCount + 1;
}
IRow rowst = sheet1.CreateRow(sCount);
//任务卡名称
ICell cell_st1 = rowst.CreateCell(0);
cell_st1.SetCellValue("总分");
cell_st1.CellStyle = style_7;
ICell cell_st2 = rowst.CreateCell(1);
cell_st2.SetCellValue("");
cell_st2.CellStyle = style_7;
ICell cell_st3 = rowst.CreateCell(2);
cell_st3.SetCellValue("");
cell_st3.CellStyle = style_7;
ICell cell_st4 = rowst.CreateCell(3);
cell_st4.SetCellValue("");
cell_st4.CellStyle = style_7;
CellRangeAddress region_st1 = new CellRangeAddress(sCount, sCount, 0, 3);
sheet1.AddMergedRegion(region_st1);
//指标数量
ICell cell_st5 = rowst.CreateCell(4);
cell_st5.SetCellValue(totalScore);
cell_st5.CellStyle = style_7;
CellRangeAddress region_st2 = new CellRangeAddress(sCount, sCount, 4, 4);
sheet1.AddMergedRegion(region_st2);
/***
* IRow row_sub = sheet1.CreateRow(5);
* ICell cell_sub = row_sub.CreateCell(0);
* cell_sub.SetCellValue("检核结果汇总");
* CellRangeAddress region_sub = new CellRangeAddress(5, 5, 0, 6);
* cell_sub.CellStyle = style_sub;
* sheet1.AddMergedRegion(region_sub);
*
* IRow row_6 = sheet1.CreateRow(6);
* ICell cell_6 = row_6.CreateCell(0);
* cell_6.SetCellValue("未达标汇总:");
* cell_6.CellStyle = style2;
* CellRangeAddress region_6 = new CellRangeAddress(6, 6, 0, 0);
* sheet1.AddMergedRegion(region_6);
*
* if (tiId != null && tiId.Count > 0)
* {
* for (int i = 0; i < tiId.Count; i++)
* {
* IRow row_un = sheet1.CreateRow(7 + i);
* ICell cell_un = row_un.CreateCell(0);
* cell_un.SetCellValue((i + 1) + "、" + tiId[i]);
* cell_un.CellStyle = style2;
* }
* }
*****/
int pw = 1;
int tcw = 1;
int tiw = 1;
int csw = 1;
int rw = 1;
if (list != null && list.Count > 0)
{
for (int k = 0; k < list.Count; k++)
{
if ((list[k].PTitle == null ? 0 : list[k].PTitle.Length) > pw)
{
pw = list[k].PTitle.Length;
}
if ((list[k].TCTitle == null ? 0 : list[k].TCTitle.Length) > tcw)
{
tcw = list[k].TCTitle.Length;
}
if ((list[k].TITitle == null ? 0 : list[k].TITitle.Length) > tiw)
{
tiw = list[k].TITitle.Length;
}
if ((list[k].CSTitle == null ? 0 : list[k].CSTitle.Length) > csw)
{
csw = list[k].CSTitle.Length;
}
if ((list[k].Remarks == null ? 0 : list[k].Remarks.Length) > rw)
{
rw = list[k].Remarks.Length;
}
}
}
//int dataCount = 10;
//if (tiId != null && tiId.Count >= 0)
//{
// dataCount = tiId.Count + 10;
//}
int dataCount = sCount + 2;
IRow row3 = sheet1.CreateRow(dataCount);
//任务卡名称
ICell cell6 = row3.CreateCell(0);
cell6.SetCellValue("模块");
cell6.CellStyle = style1;
CellRangeAddress region6 = new CellRangeAddress(dataCount, dataCount, 0, 0);
sheet1.AddMergedRegion(region6);
sheet1.SetColumnWidth(0, 7 * 600);
//体系名称
ICell cell7 = row3.CreateCell(1);
cell7.SetCellValue("审计项目");
cell7.CellStyle = style1;
CellRangeAddress region7 = new CellRangeAddress(dataCount, dataCount, 1, 1);
sheet1.AddMergedRegion(region7);
sheet1.SetColumnWidth(1, 7 * 600);
//体系权重
ICell cell8 = row3.CreateCell(2);
cell8.SetCellValue("权重");
cell8.CellStyle = style1;
CellRangeAddress region8 = new CellRangeAddress(dataCount, dataCount, 2, 2);
sheet1.AddMergedRegion(region8);
sheet1.SetColumnWidth(2, 13 * 300);
//检查标准
ICell cell9 = row3.CreateCell(3);
cell9.SetCellValue("检查标准");
cell9.CellStyle = style1;
//ICell cell10 = row3.CreateCell(4);
//cell10.SetCellValue("");
//cell10.CellStyle = style1;
CellRangeAddress region9 = new CellRangeAddress(dataCount, dataCount, 3, 3);
sheet1.AddMergedRegion(region9);
sheet1.SetColumnWidth(3, (13 * 600));
//sheet1.SetColumnWidth(4, (15 * 300));
//sheet1.SetColumnWidth(4, (15 * 300));
//结果
ICell cell_10 = row3.CreateCell(4);
cell_10.SetCellValue("是否达成");
cell_10.CellStyle = style1;
CellRangeAddress region10 = new CellRangeAddress(dataCount, dataCount, 4, 4);
sheet1.AddMergedRegion(region10);
sheet1.SetColumnWidth(4, 13 * 300);
//得分
ICell cell11 = row3.CreateCell(5);
cell11.SetCellValue("得分");
cell11.CellStyle = style1;
CellRangeAddress region11 = new CellRangeAddress(dataCount, dataCount, 5, 5);
sheet1.AddMergedRegion(region11);
sheet1.SetColumnWidth(5, 9 * 300);
//备注
ICell cell12 = row3.CreateCell(6);
cell12.SetCellValue("备注");
cell12.CellStyle = style1;
CellRangeAddress region12 = new CellRangeAddress(dataCount, dataCount, 6, 6);
sheet1.AddMergedRegion(region12);
sheet1.SetColumnWidth(6, 15 * 300);
int firstRow1 = dataCount + 1;
int lastRow1 = 0;
int sumCnt1 = 0;
int firstRow2 = dataCount + 1;
int lastRow2 = 0;
int sumCnt2 = 0;
int firstRow3 = dataCount + 1;
int lastRow3 = 0;
int sumCnt3 = 0;
int firstRow4 = dataCount + 1;
int lastRow4 = 0;
int sumCnt4 = 0;
int firstRow5 = dataCount + 1;
int lastRow5 = 0;
int sumCnt5 = 0;
//数据的动态加载
for (int i = 0; i < list.Count; i++)
{
object r = "r_" + i;
r = sheet1.CreateRow(dataCount + 1 + i);
int cnum = 0;
//计划任务名称
//任务卡名称
object c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].TCTitle);
(c as ICell).CellStyle = style_b2;
if (i == sumCnt1)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId select l1).Count();
sumCnt1 += cnt;
lastRow1 = firstRow1 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow1, lastRow1, 0, 0));
}
firstRow1 = lastRow1 + 1;
}
cnum++;
//体系名称
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].TITitle);
(c as ICell).CellStyle = style_b2;
//sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 7 + cnum, 7 + cnum));
if (i == sumCnt2)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count();
sumCnt2 += cnt;
lastRow2 = firstRow2 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow2, lastRow2, 1, 1));
}
firstRow2 = lastRow2 + 1;
}
cnum++;
//体系权重
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].TIWeight);
(c as ICell).CellStyle = style4;
//sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 7 + cnum, 7 + cnum));
if (i == sumCnt5)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count();
sumCnt5 += cnt;
lastRow5 = firstRow5 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow5, lastRow5, 2, 2));
}
firstRow5 = lastRow5 + 1;
}
cnum++;
//检查标准名称
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].CSTitle);
(c as ICell).CellStyle = style_Stand;
cnum++;
////检查标准名称
//c = "c_" + cnum;
//c = (r as IRow).CreateCell(cnum);
//(c as ICell).SetCellValue("");
//(c as ICell).CellStyle = style_Stand;
//cnum++;
//sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 2, 3));
//结果
string val = "";
if (list[i].Result != null)
{
val = list[i].Result.ToUpper() == "TRUE" ? "是" : (list[i].Result == null ? "" : "否");
}
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(val);
(c as ICell).CellStyle = style4;
sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 4, 4));
cnum++;
//得分
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].Score);
(c as ICell).CellStyle = style4;
if (i == sumCnt4)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count();
sumCnt4 += cnt;
lastRow4 = firstRow4 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow4, lastRow4, 5, 5));
}
firstRow4 = lastRow4 + 1;
}
cnum++;
//备注
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].Remarks);
(c as ICell).CellStyle = style_b2;
if (i == sumCnt3)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count();
sumCnt3 += cnt;
lastRow3 = firstRow3 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow3, lastRow3, 6, 6));
}
firstRow3 = lastRow3 + 1;
}
cnum++;
}
sheet1.CreateRow(dataCount + 1 + list.Count);
IRow row_sub2 = sheet1.CreateRow(dataCount + 2 + list.Count);
ICell cell_sub2 = row_sub2.CreateCell(0);
cell_sub2.SetCellValue("巡检照片");
CellRangeAddress region_sub2 = new CellRangeAddress(dataCount + 2 + list.Count, dataCount + 2 + list.Count, 0, 6);
cell_sub2.CellStyle = style_sub;
sheet1.AddMergedRegion(region_sub2);
for (int i = 0; i < lpList.Count; i++)
{
XSSFClientAnchor anchor;
IRow row_pic = sheet1.CreateRow(dataCount + 2 + list.Count + i + 1);
if (i % 2 == 1)
{
row_pic.Height = 4000;
anchor = new XSSFClientAnchor(50, 50, 50, 50, 4, dataCount + 2 + list.Count + i + 1, 7, dataCount + 2 + list.Count + i + 2);
}
else
{
ICell cell_pic = row_pic.CreateCell(0);
cell_pic.CellStyle = style_b3;
ICell cell_pic2 = row_pic.CreateCell(4);
cell_pic2.CellStyle = style_b3;
CellRangeAddress region_tp_1 = new CellRangeAddress(dataCount + 2 + list.Count + i + 1, dataCount + 2 + list.Count + i + 1, 0, 2);
sheet1.AddMergedRegion(region_tp_1);
CellRangeAddress region_tp_2 = new CellRangeAddress(dataCount + 2 + list.Count + i + 1, dataCount + 2 + list.Count + i + 1, 4, 6);
sheet1.AddMergedRegion(region_tp_2);
if (lpList[i].PicName != null)
{
cell_pic.SetCellValue((i + 1) + "、第" + (i + 1) + "个拍照点的名称" + lpList[i].PicName);
}
if (lpList[i + 1].PicName != null)
{
cell_pic2.SetCellValue((i + 2) + "、第" + (i + 2) + "个拍照点的名称" + lpList[i + 1].PicName);
}
anchor = new XSSFClientAnchor(50, 50, 50, 50, 0, dataCount + 2 + list.Count + i + 2, 3, dataCount + 2 + list.Count + i + 3);
}
if (lpList[i].PicUrl != null)
{
string imagesPath = lpList[i].PicUrl;
HttpClient webClient = new HttpClient();
Stream stream = await webClient.GetStreamAsync(imagesPath);
Image img = Image.FromStream(stream).GetThumbnailImage(500, 500, null, IntPtr.Zero);
MemoryStream ms = new MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
var patriarch = sheet1.CreateDrawingPatriarch();
anchor.AnchorType = AnchorType.MoveAndResize;
int index = workbook.AddPicture(ms.ToArray(), PictureType.PNG);
var signaturePicture = patriarch.CreatePicture(anchor, index);
}
}
workbook.Write(fs);
return(Json(newFile));
}
}
else
{
return(Json("没有查询结果."));
}
}
catch (Exception ex)
{
throw;
}
}
public static bool ToSingleMbExcels(string savePath, string mbPath, List <SingleMb> dataList, string gwdcode, int rownum)
{
try
{
IWorkbook workbook;
try
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003
}
}
catch (Exception ex)
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007
}
}
#region 样式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
style.WrapText = true;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontName = "宋体";
font.FontName = "Arial Narrow";
font.FontHeightInPoints = 9;
style.SetFont(font);
HSSFFont fontSmall = (HSSFFont)workbook.CreateFont();
fontSmall.FontName = "Arial Narrow";
fontSmall.FontHeightInPoints = 8;
#endregion
ISheet sheet = workbook.GetSheetAt(0);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.WrapText = true;//换行
foreach (SingleMb item in dataList)
{
IRow row = sheet.GetRow(item.rowIndex - 1);
if (row == null)
{
row = sheet.CreateRow(item.rowIndex - 1);
}
ICell cell = row.GetCell(item.cellIndex - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
//cell.CellStyle = cellStyle;
cell.SetCellValue(item.value); //循环往第二行的单元格中添加数据
}
#region 插入图片
string gwdgz = gwdcode;
string imageFilePath = HttpContext.Current.Server.MapPath("/Content/TASK/images/");
if (System.IO.File.Exists(imageFilePath + gwdgz + ".png"))
{
byte[] bytes = System.IO.File.ReadAllBytes(imageFilePath + gwdgz + ".png");
//int pictureIdx = myWorkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
// Create the drawing patriarch. This is the top level container for all shapes.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//add a picture
//dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
//dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
//dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
//dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
//col1：起始单元格列序号，从0开始计算；
//row1：起始单元格行序号，从0开始计算，如例子中col1=0,row1=0就表示起始单元格为A1；
//col2：终止单元格列序号，从0开始计算；
//row2：终止单元格行序号，从0开始计算，如例子中col2=2,row2=2就表示起始单元格为C3；
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 0, 14, 4);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//anchor = new HSSFClientAnchor(0, 0, 0, 0, 13, 11, 15, 14);
//HSSFPicture pict1 = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();
}
#endregion
#region 放入问题表格边框
for (int i = 23; i < rownum - 1; i++)
{
for (int j = 0; j <= 13; j++)
{
sheet.GetRow(i).GetCell(j).CellStyle = style;
if (j == 0)
{
sheet.GetRow(i).GetCell(j).CellStyle.SetFont(fontSmall);
}
}
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
/// <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>
/// 集合导出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);
}
public static void writeExcel(List <Product> products)
{
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Products");
ws.SetColumnWidth(0, 6000);
IRow header = ws.CreateRow(0);
header.CreateCell(0).SetCellValue("Name");
header.CreateCell(1).SetCellValue("Price");
header.CreateCell(2).SetCellValue("Old Price");
header.CreateCell(3).SetCellValue("Difference");
header.CreateCell(4).SetCellValue("Category");
header.CreateCell(5).SetCellValue("Picture");
header.CreateCell(6).SetCellValue("ID");
header.CreateCell(7).SetCellValue("URL");
int rowcount = 1;
foreach (Product product in products)
{
IRow ProductRow = ws.CreateRow(rowcount);
ProductRow.CreateCell(0).SetCellValue(product.name);
ProductRow.CreateCell(1).SetCellValue(product.price);
ProductRow.CreateCell(2).SetCellValue(product.xprice);
ProductRow.CreateCell(3).SetCellValue(product.dif);
ProductRow.CreateCell(4).SetCellValue(product.category);
ProductRow.CreateCell(5);
ProductRow.CreateCell(6).SetCellValue(product.ID);
ProductRow.CreateCell(7).SetCellValue(product.URL);
ProductRow.Height = 1500;
if (File.Exists(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png"))
{
byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png");
int pictureIndex = wb.AddPicture(data, PictureType.PNG);
IDrawing patriarch = ws.CreateDrawingPatriarch();
IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = 5;
anchor.Row1 = rowcount;
anchor.AnchorType = AnchorType.MoveAndResize;
IPicture picture = patriarch.CreatePicture(anchor, pictureIndex);
picture.Resize(1);
//byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png");
//int pictureIndex = wb.AddPicture(data, PictureType.PNG);
//ICreationHelper helper = wb.GetCreationHelper();
//IDrawing drawing = ws.CreateDrawingPatriarch();
//IClientAnchor anchor = helper.CreateClientAnchor();
//anchor.Col1 = 5;//0 index based column
//anchor.Row1 = ProductRow.RowNum;//0 index based row
//IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
//picture.Resize();
}
rowcount++;
}
IRow row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("Stop");
rowcount++;
row = ws.CreateRow(rowcount);
rowcount++;
row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("Skipped products");
rowcount++;
foreach (String url in Program.skippedURLs)
{
row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue(url);
rowcount++;
}
Stream stream = new FileStream(ProductsExcelPath, FileMode.Create);
//Stream stream = new FileStream(@"C:\Users\email\Desktop\Hardware Hub\products.xlsx", FileMode.Open);
wb.Write(stream);
wb.Close();
stream.Close();
}
/// <summary>
/// 生成EXCEL
/// </summary>
/// <param name="info">数据</param>
/// <param name="sSheetName">EXCEL生成后的路径，绝对路径如：C:\a.xls</param>
public ImportExcelHelper(List <DataListViewModel> info, string sSheetName)
{
FileStream fs = null;
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("测试列表"); //创建一个名称为XX的表
sheet.CreateFreezePane(0, 1); //冻结列头行
IRow row_Title = sheet.CreateRow(0); //创建列头行
row_Title.HeightInPoints = 19.5F; //设置列头行高
// 总列
var row = 2;
#region 设置列宽
// 设置列宽,excel列宽每个像素是1/256
for (int i = 0; i <= row; i++)
{
switch (i)
{
case 0:
sheet.SetColumnWidth(i, 10 * 256);
break;
case 1:
sheet.SetColumnWidth(i, 20 * 256);
break;
}
}
#endregion
#region 设置列头单元格样式
ICellStyle cs_Title = wb.CreateCellStyle(); //创建列头样式
cs_Title.Alignment = HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = VerticalAlignment.Center; //垂直居中
IFont cs_Title_Font = wb.CreateFont(); //创建字体
cs_Title_Font.IsBold = true; //字体加粗
cs_Title_Font.FontHeightInPoints = 12; //字体大小
cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
#endregion
#region 生成列头
for (int i = 0; i <= row; i++)
{
ICell cell_Title = row_Title.CreateCell(i); //创建单元格
cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
switch (i)
{
case 0:
cell_Title.SetCellValue("序号");
break;
case 1:
cell_Title.SetCellValue("图片");
break;
}
}
#endregion
if (info != null)
{
for (int i = 0; i < info.Count; i++)
{
#region 设置内容单元格样式
ICellStyle cs_Content = wb.CreateCellStyle(); //创建列头样式
cs_Content.Alignment = HorizontalAlignment.Center; //水平居中
cs_Content.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cs_Content.WrapText = true; // 自动换行
IFont cs_Content_Font = wb.CreateFont(); //创建字体
cs_Content_Font.FontHeightInPoints = 12; //字体大小
cs_Content.SetFont(cs_Content_Font); //将字体绑定到样式
#endregion
#region 生成内容单元格
//创建行
IRow row_Content = sheet.CreateRow(i + 1);
//设置行高 ,excel行高度每个像素点是1/20
row_Content.Height = 100 * 20;
for (int j = 0; j <= row; j++)
{
ICell cell_Conent = row_Content.CreateCell(j); //创建单元格
cell_Conent.CellStyle = cs_Content;
switch (j)
{
case 0:
SetCellValue(cell_Conent, info[i].ID);
break;
case 1:
if (!string.IsNullOrEmpty(info[i].ImgPath))
{
// 第一步：读取图片到byte数组
var filename = GetImageRoute(info[i].ImgPath);
if (!string.IsNullOrEmpty(filename))
{
byte[] bytes = File.ReadAllBytes(filename);
// 第二步：将图片添加到workbook中 指定图片格式 返回图片所在workbook->Picture数组中的索引地址（从1开始）
int pictureIdx = wb.AddPicture(bytes, PictureType.JPEG);
// 第三步：在sheet中创建画部
IDrawing drawing = sheet.CreateDrawingPatriarch();
// 第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）
IClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2);
// 第五步：创建图片
IPicture picture = drawing.CreatePicture(anchor, pictureIdx);
// 删除本地图片
DeleteImage(filename);
}
}
break;
}
}
#endregion
}
}
using (fs = File.OpenWrite(sSheetName))
{
wb.Write(fs);//向打开的这个xls文件中写入数据
}
}
/// <summary>
/// 导出Excel DataTable
/// </summary>
/// <param name="records">records必须都为DataTable</param>
/// <param name="formatter">Dictionary key:DataTable中的列明此处必须小写 value:EnumColumnTrans</param>
/// <returns></returns>
public byte[] Write(DataTable records, Dictionary <string, ExcelFormatter> formatter = null, string imgBasepath = "")
{
Stopwatch sw = new Stopwatch();
sw.Start();
var workBook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
var sheet = workBook.CreateSheet();
var headerRow = sheet.CreateRow(0);
var cellIndex = 0;
Color lightGrey = Color.FromArgb(221, 221, 221);
ICellStyle cstyle = workBook.CreateCellStyle();
cstyle.Alignment = HorizontalAlignment.Center;
cstyle.IsLocked = true;
// cstyle.FillForegroundColor = new XSSFColor(lightGrey).Indexed;
cstyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
foreach (var map in Maps)
{
var hcell = headerRow.CreateCell(cellIndex, CellType.String);
hcell.CellStyle = cstyle;
hcell.SetCellValue(map.Name);
cellIndex++;
}
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
var rowIndex = 1;
IDrawing patriarch = sheet.CreateDrawingPatriarch();
bool isimg = false;
foreach (DataRow record in records.Rows)
{
var dr = sheet.CreateRow(rowIndex);
for (int i = 0; i < Maps.Count; i++)
{
string drValue = record[Maps[i].Info.ToString()].ToString();
ICell cell = dr.CreateCell(i);
if (formatter.Any() && formatter.ContainsKey(Maps[i].Info.ToLower()) && formatter[Maps[i].Info.ToLower()] != null)
{
ExcelFormatter excelFormatter = formatter[Maps[i].Info.ToLower()];
if (!string.IsNullOrEmpty(drValue))
{
if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertDownList)
{
cell.SetCellValue(drValue);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(excelFormatter.DropSource.Split(','));
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, i, i);
XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);
sheet.AddValidationData(dataValidate);
}
else if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertImage)
{
if (File.Exists(@"" + imgBasepath + drValue))
{
if (!isimg)
{
sheet.SetColumnWidth(i, 256 * 20);
isimg = true;
}
dr.HeightInPoints = 90;
byte[] bytes = System.IO.File.ReadAllBytes(@"" + imgBasepath + drValue);
int pictureIdx = workBook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
IClientAnchor anchor = new XSSFClientAnchor(100, 50, 0, 0, i, rowIndex, i + 1,
rowIndex + 1);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(0.3);
}
else
{
cell.SetCellValue("");
}
}
else
{
cell.SetCellValue(FormatterCoulumn(drValue, excelFormatter.ColumnTrans));
}
}
else
{
cell.SetCellValue(drValue);
}
}
else
{
switch (records.Columns[Maps[i].Info].DataType.ToString())
{
case "System.String": //字符串类型
cell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
//cell.CellStyle =
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
}
rowIndex++;
}
workBook.Write(ms);
byte[] buffer = ms.ToArray();
ms.Close();
sw.Stop();
return(buffer);
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
private void DiseñoCabeceras(int sheet)
{
if (sheet < 0)
{
throw new ArgumentOutOfRangeException(nameof(sheet));
}
var pestana = _excel.GetSheetAt(sheet);
var image = Image.FromFile(@"C:/Users/mario.chan/Documents/GitHub/Benchmark/Library_benchmark/Content/images/Cemex.png");
for (var i = 0; i < 2; i++)
{
var row = pestana.GetRow(i) ?? pestana.CreateRow(i);
row.HeightInPoints = 51f;
for (var j = 7; j < 13; j++)
{
if (row.GetCell(j) == null)
{
row.CreateCell(j);
}
}
}
var cra = new CellRangeAddress(0, 1, 8, 12);
pestana.AddMergedRegion(cra);
if (_cabeceraStyle == null)
{
_cabeceraStyle = GetCabeceraCellStyle();
}
var celda = pestana.GetRow(0).GetCell(8);
celda.SetCellValue("NPOI");
celda.CellStyle = _cabeceraStyle;
////row0.HeightInPoints = (float)image.Height;
var converter = new ImageConverter();
var data = (byte[])converter.ConvertTo(image, typeof(byte[]));
//var pictureIndex = excel.AddPicture(data, PictureType.PNG);
//var helper = excel.GetCreationHelper();
//var drawing = pestana.CreateDrawingPatriarch();
//var anchor = new XSSFClientAnchor(900, 0, 0, 0, 1, 1, 7, 2);
//var picture = drawing.CreatePicture(anchor, pictureIndex);
//picture.Resize(1);
var myPictureId = _excel.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG);
var drawing = pestana.CreateDrawingPatriarch();
var myAnchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 7, 2);
var myPicture = drawing.CreatePicture(myAnchor, myPictureId);
myPicture.Resize();
}
public async Task <ActionResult> UploadScoreAjaxDown(string disCode, string startTime, string endTime, string status, string Pid, string disname, string name)
{
try
{
string result = await CommonHelper.GetHttpClient().GetStringAsync(CommonHelper.Current.GetAPIBaseUrl + "Tour/GetTaskListByDisIdForExcel/" + disCode + "/" + startTime + "/" + endTime + "/" + status + "/" + Pid);
var apiResult = CommonHelper.DecodeString <APIResult>(result);
if (apiResult.ResultCode == ResultType.Success)
{
ExcelResult er = CommonHelper.DecodeString <ExcelResult>(apiResult.Body);
List <ResultExcelDto> listAll = er.ResultList;
string discode = listAll.FirstOrDefault <ResultExcelDto>().DisCode;
List <ResultExcelDto> list = (from a in listAll where a.Score == "0" select a).ToList <ResultExcelDto>();
var uploads = Path.Combine(_environment.WebRootPath, "Template");
var newFile = Path.Combine(uploads, "ScoreViewList_" + discode + "_" + disname + DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xlsx");
if (!System.IO.Directory.Exists(uploads))
{
System.IO.Directory.CreateDirectory(uploads);
}
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("得分登记");
sheet1.PrintSetup.Landscape = true;
sheet1.PrintSetup.PaperSize = 9;
IFont font_b = workbook.CreateFont();
font_b.FontName = "Microsoft Yahei";
font_b.FontHeightInPoints = 10;
var style1 = (XSSFCellStyle)workbook.CreateCellStyle();
//style1.FillForegroundColor = HSSFColor.Grey25Percent.Index;
byte[] rgb = new byte[3] {
116, 163, 210
};
style1.SetFillForegroundColor(new XSSFColor(rgb));
style1.FillPattern = FillPattern.SolidForeground;
style1.Alignment = HorizontalAlignment.Center;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderBottom = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.SetFont(font_b);
var style_Stand = workbook.CreateCellStyle();
style_Stand.Alignment = HorizontalAlignment.Left;
style_Stand.WrapText = true;
style_Stand.SetFont(font_b);
var style_b2 = workbook.CreateCellStyle();
style_b2.WrapText = true;
style_b2.VerticalAlignment = VerticalAlignment.Center;
style_b2.SetFont(font_b);
var style2 = workbook.CreateCellStyle();
style2.Alignment = HorizontalAlignment.Left;
style2.VerticalAlignment = VerticalAlignment.Bottom;
style2.WrapText = true;
style2.SetFont(font_b);
var style3 = workbook.CreateCellStyle();
style3.Alignment = HorizontalAlignment.Right;
style3.SetFont(font_b);
var style4 = workbook.CreateCellStyle();
style4.Alignment = HorizontalAlignment.Center;
style4.VerticalAlignment = VerticalAlignment.Center;
style4.SetFont(font_b);
var style_t = (XSSFCellStyle)workbook.CreateCellStyle();
style_t.Alignment = HorizontalAlignment.Center;
style_t.SetFillForegroundColor(new XSSFColor(rgb));
//style_t.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style_t.FillPattern = FillPattern.SolidForeground;
IFont font_t = workbook.CreateFont();
font_t.FontHeightInPoints = 18;
font_t.FontName = "Microsoft Yahei";
style_t.SetFont(font_t);
//第一行，插入图片
IRow row_0 = sheet1.CreateRow(0);
row_0.Height = 600;
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 8);
sheet1.AddMergedRegion(region0);
byte[] bytes = System.IO.File.ReadAllBytes(_environment.WebRootPath + "/images/project_logo.png");
int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
var patriarch = sheet1.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 200, 40, 0, 0, 1, 1);
anchor.AnchorType = AnchorType.MoveAndResize;
//把图片插到相应的位置
var pict = patriarch.CreatePicture(anchor, pictureIdx);
//标题
IRow row_t = sheet1.CreateRow(1);
ICell cell_t = row_t.CreateCell(0);
cell_t.SetCellValue("一汽丰田PCM经销店经营能力评估-待改善项报告");
CellRangeAddress region_t = new CellRangeAddress(1, 1, 0, 4);
cell_t.CellStyle = style_t;
sheet1.AddMergedRegion(region_t);
//插入标题右边的Logo
byte[] bytesRight = System.IO.File.ReadAllBytes(_environment.WebRootPath + "/images/pcm_logo.png");
int pictureIdxRight = workbook.AddPicture(bytesRight, PictureType.PNG);
var patriarchRight = sheet1.CreateDrawingPatriarch();
XSSFClientAnchor anchorRight = new XSSFClientAnchor(0, 0, 0, 0, 6, 1, 7, 3);
//把图片插到相应的位置
var pictRight = patriarch.CreatePicture(anchorRight, pictureIdxRight);
pictRight.Resize(2.01, 1.5);
IRow row = sheet1.CreateRow(2);
row.Height = 600;
ICell cell = row.CreateCell(0);
cell.SetCellValue("经销店代码:" + discode + " 经销店名称：" + disname + " 经销店负责人签字：________________");
CellRangeAddress region = new CellRangeAddress(2, 2, 0, 4);
cell.CellStyle = style2;
sheet1.AddMergedRegion(region);
IRow row1 = sheet1.CreateRow(3);
row1.Height = 600;
ICell cel2 = row1.CreateCell(0);
//cel2.CellStyle = style;
cel2.SetCellValue("日期：" + startTime + " 评估员：" + name);
cel2.CellStyle = style2;
CellRangeAddress region2 = new CellRangeAddress(3, 3, 0, 4);
sheet1.AddMergedRegion(region2);
var style_sub = (XSSFCellStyle)workbook.CreateCellStyle();
style_sub.Alignment = HorizontalAlignment.Center;
style_sub.SetFillForegroundColor(new XSSFColor(rgb));
style_sub.FillPattern = FillPattern.SolidForeground;
style_sub.SetFont(font_b);
IRow row_nouse = sheet1.CreateRow(4);
row_nouse.Height = 400;
IRow row_sub = sheet1.CreateRow(5);
ICell cell_sub = row_sub.CreateCell(0);
cell_sub.SetCellValue("待改善项汇总");
CellRangeAddress region_sub = new CellRangeAddress(5, 5, 0, 8);
cell_sub.CellStyle = style_sub;
sheet1.AddMergedRegion(region_sub);
int dataCount = 6;
IRow row3 = sheet1.CreateRow(dataCount);
//任务卡名称
ICell cell6 = row3.CreateCell(0);
cell6.SetCellValue("指标名称");
cell6.CellStyle = style1;
CellRangeAddress region6 = new CellRangeAddress(dataCount, dataCount, 0, 0);
sheet1.AddMergedRegion(region6);
sheet1.SetColumnWidth(0, 9 * 600);
//体系名称
ICell cell7 = row3.CreateCell(1);
cell7.SetCellValue("标准与要求");
cell7.CellStyle = style1;
CellRangeAddress region7 = new CellRangeAddress(dataCount, dataCount, 1, 1);
sheet1.AddMergedRegion(region7);
sheet1.SetColumnWidth(1, 8 * 600);
//检查标准
ICell cell8 = row3.CreateCell(2);
cell8.SetCellValue("过程与关键动作建议");
cell8.CellStyle = style1;
ICell cell9 = row3.CreateCell(3);
cell9.SetCellValue("");
cell9.CellStyle = style1;
ICell cell_9 = row3.CreateCell(4);
cell_9.SetCellValue("");
cell_9.CellStyle = style1;
CellRangeAddress region8 = new CellRangeAddress(dataCount, dataCount, 2, 4);
sheet1.AddMergedRegion(region8);
sheet1.SetColumnWidth(2, (15 * 600));
sheet1.SetColumnWidth(3, (3 * 600));
sheet1.SetColumnWidth(4, (10 * 600));
//结果
//ICell cell9 = row3.CreateCell(3);
// cell9.SetCellValue("结果");
// cell9.CellStyle = style1;
//CellRangeAddress region9 = new CellRangeAddress(dataCount, dataCount, 4, 4);
//sheet1.AddMergedRegion(region9);
//得分
ICell cell10 = row3.CreateCell(5);
cell10.SetCellValue("是否合格");
cell10.CellStyle = style1;
ICell cell101 = row3.CreateCell(6);
cell101.SetCellValue("");
cell101.CellStyle = style1;
CellRangeAddress region10 = new CellRangeAddress(dataCount, dataCount, 5, 6);
sheet1.AddMergedRegion(region10);
sheet1.SetColumnWidth(5, 4 * 300);
sheet1.SetColumnWidth(6, 4 * 300);
//备注
ICell cell11 = row3.CreateCell(7);
cell11.SetCellValue("备注");
cell11.CellStyle = style1;
ICell cell111 = row3.CreateCell(8);
cell111.SetCellValue("");
cell111.CellStyle = style1;
CellRangeAddress region11 = new CellRangeAddress(dataCount, dataCount, 7, 8);
sheet1.AddMergedRegion(region11);
sheet1.SetColumnWidth(7, 5 * 600);
sheet1.SetColumnWidth(8, 3 * 600);
int firstRow1 = dataCount + 1;
int lastRow1 = 0;
int sumCnt1 = 0;
int firstRow2 = dataCount + 1;
int lastRow2 = 0;
int sumCnt2 = 0;
int firstRow3 = dataCount + 1;
int lastRow3 = 0;
int sumCnt3 = 0;
//数据的动态加载
for (int i = 0; i < list.Count; i++)
{
object r = "r_" + i;
r = sheet1.CreateRow(dataCount + 1 + i);
int cnum = 0;
//计划任务名称
//任务卡名称
object c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].TCTitle);
(c as ICell).CellStyle = style_b2;
if (i == sumCnt1)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId select l1).Count();
sumCnt1 += cnt;
lastRow1 = firstRow1 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow1, lastRow1, 0, 0));
}
firstRow1 = lastRow1 + 1;
}
cnum++;
//体系名称
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].TITitle);
(c as ICell).CellStyle = style_b2;
//sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 7 + cnum, 7 + cnum));
if (i == sumCnt2)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count();
sumCnt2 += cnt;
lastRow2 = firstRow2 + cnt - 1;
if (list[i].CSId != null)
{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow2, lastRow2, 1, 1));
}
firstRow2 = lastRow2 + 1;
}
cnum++;
//检查标准名称
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].CSTitle);
(c as ICell).CellStyle = style_Stand;
//sheet1.AddMergedRegion(new CellRangeAddress(dataCount + i, dataCount + i, 7 + cnum, 7 + cnum));
//sheet1.AddMergedRegion(new CellRangeAddress(dataCount + i, dataCount + i, 2, 3));
cnum++;
//检查标准名称
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue("");
(c as ICell).CellStyle = style_Stand;
cnum++;
//检查标准名称
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue("");
(c as ICell).CellStyle = style_Stand;
sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 2, 4));
cnum++;
//结果
/*c = "c_" + cnum;
* c = (r as IRow).CreateCell(cnum);
* (c as ICell).SetCellValue(list[i].Result == "False" ? "否" : (list[i].Result == null ? "" : "是"));
* (c as ICell).CellStyle = style4;
* sheet1.AddMergedRegion(new CellRangeAddress(dataCount + i, dataCount + i, 7 + cnum, 7 + cnum));
* cnum++;*/
//得分
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].Result == "true" ? "否" : "是");
(c as ICell).CellStyle = style4;
cnum++;
//得分
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue("");
(c as ICell).CellStyle = style4;
sheet1.AddMergedRegion(new CellRangeAddress(dataCount + 1 + i, dataCount + 1 + i, 5, 6));
cnum++;
//备注
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue(list[i].Remarks);
(c as ICell).CellStyle = style_b2;
cnum++;
//备注
c = "c_" + cnum;
c = (r as IRow).CreateCell(cnum);
(c as ICell).SetCellValue("");
(c as ICell).CellStyle = style_b2;
if (i == sumCnt3)
{
int cnt = (from l1 in list where l1.TCId == list[i].TCId && l1.TIId == list[i].TIId select l1).Count();
sumCnt3 += cnt;
lastRow3 = firstRow3 + cnt - 1;
//if (list[i].CSId != null)
//{
sheet1.AddMergedRegion(new CellRangeAddress(firstRow3, lastRow3, 7, 8));
//}
firstRow3 = lastRow3 + 1;
}
cnum++;
}
workbook.Write(fs);
return(Json(newFile));
}
}
else
{
return(Json("没有查询结果."));
}
}
catch (Exception)
{
throw;
}
}
public static void ToExcel2007 <T>(List <T> dataSource, string path, List <string> attries, List <string> headers)
{
if (dataSource == null || !dataSource.Any())
{
throw new Exception("dataSource is null.");
}
var workbook = new XSSFWorkbook(); //创建一个工作薄
ISheet sheet = workbook.CreateSheet(); //在工作薄中创建一个工作表
IRow rw = sheet.CreateRow(0);
var patriarch = sheet.CreateDrawingPatriarch();
for (int i = 0; i < headers.Count; i++) //循环一个表头来创建第一行的表头
{
rw.CreateCell(i).SetCellValue(headers[i]);
}
Type t = typeof(T); //获取得泛型集合中的实体， 返回T的类型
PropertyInfo[] properties = t.GetProperties(); //返回当前获得实体后 实体类型中的所有公共属性
for (int i = 0; i < dataSource.Count; i++) //循环实体泛型集合
{
rw = sheet.CreateRow(i + 1); //创建一个新行，把传入集合中的每条数据创建一行
foreach (PropertyInfo property in properties) //循环得到的所有属性（想要把里面指定的属性值导出到Excel文件中）
{
for (int j = 0; j < attries.Count; j++) //循环需要导出属性值 的 属性名
{
string attry = attries[j]; //获得一个需要导入的属性名；
if (string.Compare(property.Name.ToUpper(), attry.ToUpper()) == 0) //如果需要导出的属性名和当前循环实体的属性名一样，
{
object objValue = property.GetValue(dataSource[i], null); //获取当前循环的实体属性在当前实体对象（arr[i]）的值
if (objValue != null && (objValue.GetType().Name == "Bitmap" || objValue.GetType().Name == "Image"))
{
//- 插入图片到 Excel，并返回一个图片的标识
var handle = (objValue as Bitmap).GetHbitmap();
using (Bitmap newBmp = Image.FromHbitmap(handle))
{
MemoryStream ms = new MemoryStream();
newBmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bytes = ms.GetBuffer();
ms.Close();
var pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
//- 创建图片的位置
var anchor = new XSSFClientAnchor(
0, 0, //- 上左 到 上右 的位置，是基于下面的行列位置
0, 0, //- 下左 到 下右 的位置，是基于下面的行列位置
j, i + 1,
j + 1, i + 2);
//- 图片输出的位置这么计算的：
//- 假设我们要将图片放置于第 5(E) 列的第 2 行
//- 对应索引为是 4 : 1 （默认位置）
//- 放置的位置就等于（默认位置）到（默认位置各自加上一行、一列）
var pic = patriarch.CreatePicture(anchor, pictureIdx);//- 使用绘画器绘画图片
sheet.SetColumnWidth(j, 100 * 36);
rw.HeightInPoints = 100 * 0.75f;
bytes = null;
}
DeleteObject(handle);
}
else
{
rw.CreateCell(j).SetCellValue((objValue == null) ? string.Empty : objValue.ToString());//创建单元格并进行赋值
sheet.AutoSizeColumn(j);
}
}
}
}
}
using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
}
