Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
Nine leaves heavy floor two, winter solstice cicada pupa a money, fried into the next year’s snow, can cure the world’s acacia suffering.
Can be heavy floor seven leaves a flower, winter solstice how cicada pupa, snow and other years, the original is acacia without solution.
Little do they know, summer withered is nine leaves heavy floor, digging three feet of cicada, New Year’s Eve zishi snow, landing has been another year.
After parting, acacia can be solved.
Net export function is estimated to be the major system than the lack of functions, this paper mainly records Excel with image export function in some matters needing attention. This article export Excel is implemented with EPPlus.
So here’s the code…
#region 生成Excel
string sFileName = DataParseHelper.GetDateYMDHMS(DateTime.Now) + ".xlsx";
string path = AppSettingsReader.UploadResourcePath + "/OutPutExcel/统计/";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
FileInfo file = new FileInfo(path + sFileName);
using (ExcelPackage package = new ExcelPackage(file))
{
int index = 0;
try
{
#region 添加worksheet1
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("固定资产");
worksheet.PrinterSettings.PaperSize = ePaperSize.A3;//纸张大小
worksheet.PrinterSettings.Orientation = eOrientation.Landscape;//页面横向
worksheet.DefaultRowHeight = 60;
worksheet.DefaultColWidth = 15;
//设置列宽
worksheet.Column(++index).Width = 5;
worksheet.Column(++index).Width = 13;
worksheet.Column(++index).Width = 13;
worksheet.Column(++index).Width = 15;
//worksheet.Column(++index).Width = 15;//注释后下面设置图片的位置才可以
worksheet.Cells[1, 1, 1, index + 1].Merge = true;
worksheet.Cells[1, 1].Value = companyName + "_______________统计明细表";
worksheet.Row(1).Style.Font.Bold = true;
worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //居中
worksheet.Row(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center; //居中
worksheet.Row(1).Style.Font.Size = 14; //字体大小
worksheet.Row(1).Style.Font.Name = "宋体";
worksheet.Row(1).Height = 40; //表头行高
worksheet.Cells.Style.WrapText = true;
//添加头
index = 0;
worksheet.Cells[2, ++index].Value = "序号";
worksheet.Cells[2, ++index].Value = "资产类别";
worksheet.Cells[2, ++index].Value = "资产名称";
worksheet.Cells[2, ++index].Value = "SN号";
worksheet.Cells[2, ++index].Value = "备注";
worksheet.Row(2).Style.Font.Bold = true; //字体为粗体 worksheet.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Row(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Row(2).Style.Font.Size = 12;
worksheet.Row(2).Style.Font.Name = "宋体";
worksheet.Cells[2, 1, 2, index].Style.Fill.PatternType = ExcelFillStyle.Solid;
//边框
worksheet.Cells[1, 1, returnList.Count + 3, index].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, 1, returnList.Count + 3, index].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, 1, returnList.Count + 3, index].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, 1, returnList.Count + 3, index].Style.Border.Right.Style = ExcelBorderStyle.Thin;
int i = 0;
int allcheck = 0;
int alllistcount = 0;
for (int j = 0; j < returnList.Count; j++)
{
AssetInfoOtherView item = returnList[j];
index = 0;
List<AssetInfoOtherView> chilalist = returnList.Where(x => x.AssetParentId == item.AssetId).ToList();
if (chilalist.Count > 0)//说明有子集 需要合并单元格
{
worksheet.Cells[i + 3, 2, i + 3 + chilalist.Count, 2].Merge = true;
worksheet.Cells[i + 3, 3, i + 3 + chilalist.Count, 3].Merge = true;
worksheet.Cells[i + 3, 4, i + 3 + chilalist.Count, 4].Merge = true;
}
worksheet.Row(i + 3).Height = 60;
worksheet.Row(i + 3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
worksheet.Row(i + 3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells[i + 3, ++index].Value = i + 1;
worksheet.Cells[i + 3, ++index].Value = item.AssetTypeName;
worksheet.Cells[i + 3, ++index].Value = item.AssetName;
worksheet.Cells[i + 3, ++index].Value = item.AssetCode;
#region
if (containImg == "1")
{
try
{
if (string.IsNullOrEmpty(item.CheckImgUrl))//图片为空
{
worksheet.Cells[i + 3, ++index].Value = "";// item.CheckImgUrl;
}
else
{
string imgpath = AppSettingsReader.UploadResourceUrl + item.CheckImgUrl;
Image img = Image.FromStream(WebRequest.Create(imgpath).GetResponse().GetResponseStream());
int toWidth = 1048;
Image imgmid;
if (img.Width > toWidth || img.Height > toWidth)//图片过大的情况下需要压缩
{
imgpath = CompressImage(imgpath, toWidth, item.CheckImgUrl);
imgmid = Image.FromFile(imgpath);
}
else
{
imgmid = img;
}
if (imgmid.Width > imgmid.Height)//宽图显示失败，所以做下旋转后再显示
{
Image Roimg = RotateImg(imgmid, 90);//旋转后直接AddPicture报错，只能旋转后再次保存，读取保存后的图片。待优化。
//Random rd = new Random();
//int sindex = imgpath.LastIndexOf('/');
//string newpath = imgpath.Substring(0, sindex + 1) + rd.Next() + ".jpg";
//保存缩略图
Random rd = new Random();
string savepath = AppSettingsReader.UploadResourcePath + "/OutPutExcel/Temp/" + rd.Next() + ".jpg";
Roimg.Save(savepath, ImageFormat.Jpeg);
Image newimg = Image.FromFile(savepath);
ExcelPicture pic = worksheet.Drawings.AddPicture(i.ToString(), newimg);
pic.SetPosition(i + 3 - 1, 0, ++index - 1, 0);//设置的位置是以0为开始索引的，我们实际表中是以1为开始索引的，所以需要减1
pic.SetSize(80, 80);
}
else
{
string[] arrpath = imgpath.Split('.');
if (arrpath.Length > 1)
{
Image newimg;
if (arrpath[arrpath.Length - 1] != "jpg")
{
imgpath = ImgOtherToJpg(imgpath, item.CheckImgUrl);//实际项目中有其他格式的图片，会导致导出报错,固此处转换成jpg的
newimg = Image.FromFile(imgpath);
}
else
{
newimg = imgmid;
}
ExcelPicture pic = worksheet.Drawings.AddPicture(i.ToString(), newimg);
pic.SetPosition(i + 3 - 1, 0, ++index - 1, 0);//设置的位置是以0为开始索引的，我们实际表中是以1为开始索引的，所以需要减1
pic.SetSize(80, 80);
}
else
{
worksheet.Cells[i + 3, ++index].Value = "";// item.CheckImgUrl;
}
}
}
}
catch (Exception ex)
{
LogHelper.CurrentLogger.Error(ex.Message, ex);
worksheet.Cells[i + 3, ++index].Value = "图片异常";// item.CheckImgUrl;
}
}
#endregion
alllistcount += item.AssetCount;
i++;
}
#endregion
package.Save();
}
catch (Exception ex)
{
LogHelper.CurrentLogger.Error(ex.Message, ex);
resultModel = ResponseModel.EasyUiPageListException(0, null, ex.Message);
return this.Json(resultModel);
}
}
#endregion
Copy the code## Attention
1, the image header does not need to set the length, otherwise it will not be displayed;
2. The position of the picture in the table is indexed with 0 as the beginning, but the actual table is indexed with 1 as the beginning, so remember index minus 1;
3. The obtained picture should not be too large, which will affect the export speed;
4. The best picture to get is the vertical one. The width of the horizontal one should not be too large.
5, the image format is best JPG, other may report errors.
Attached is the processing method of the picture
/// </summary> /// </returns> </returns> public string CompressImage(string sFile, string sFile) int toWidth, string pathStr) { //Image initimage = Image.FromFile(sFile); Image initimage = Image.FromStream(WebRequest.Create(sFile).GetResponse().GetResponseStream()); if (initimage.Width > toWidth || initimage.Height > toWidth) { int toHeight; if (initimage.Width > initimage.Height) { toHeight = initimage.Height * toWidth / initimage.Width; } else { toHeight = toWidth; toWidth = initimage.Width * toHeight / initimage.Height; Newimage = new Bitmap((int)toWidth, (int)toHeight); // Create a new artboard Graphics newg = graphics.fromImage (newimage); / / set quality newg InterpolationMode = System. Drawing. Drawing2D. InterpolationMode. HighQualityBicubic; newg.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality; // set the background Color newg.Clear(color.white); Newg.DrawImage(initimage, new Rectangle(0, 0, newImage.width, newImage.height), new Rectangle(0, 0, Rectangle(0, 0)), new Rectangle(0, 0) initimage.Width, initimage.Height), GraphicsUnit.Pixel); Rd = new Random(); string savepath = AppSettingsReader.UploadResourcePath + "/OutPutExcel/Temp/" + rd.Next() + ".jpg"; newimage.Save(savepath, System.Drawing.Imaging.ImageFormat.Jpeg); // Dispose(); newimage.Dispose(); initimage.Dispose(); return savepath; } else { return sFile; } } #endregionCopy the codePublic Image RotateImg(Image img, float Angle) {// Use Png to set Image transparency. int width = img.Width; int height = img.Height; // Matrix MTRX = new Matrix(); mtrx.RotateAt(angle, new PointF((width / 2), (height / 2)), MatrixOrder.Append); // Get the rotated rectangle GraphicsPath path = new GraphicsPath(); path.AddRectangle(new RectangleF(0f, 0f, width, height)); RectangleF rct = path.GetBounds(mtrx); DevImage = new Bitmap((int)(rct.width), (int)(rct.height)); devImage = new Bitmap((int)(rct.height)); Graphics g = Graphics.FromImage(devImage); g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.Bilinear; g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality; // Calculate Offset Point Offset = new Point((int)(rct.width-width) / 2, (int)(rct.height-height) / 2); Rectangle rect = new Rectangle(Offset.X, Offset.Y, (int)width, (int)height); Rectangle rect = new Rectangle(Offset. Point center = new Point((int)(rect.X + rect.Width / 2), (int)(rect.Y + rect.Height / 2)); g.TranslateTransform(center.X, center.Y); g.RotateTransform(angle); G ranslateTransform(-center.x, -center.y); g.DrawImage(img, rect); G.resettransform (); g.Save(); g.Dispose(); path.Dispose(); return devImage; } #endregionCopy the codePublic string ImgOtherToJpg(string StartPath, string pathStr) { //Image img = Image.FromFile(StartPath); Image img = Image.FromStream(WebRequest.Create(StartPath).GetResponse().GetResponseStream()); using (var bmp = new Bitmap(img.Width, img.Height)) { bmp.SetResolution(img.HorizontalResolution, img.VerticalResolution); using (var g = Graphics.FromImage(bmp)) { g.Clear(Color.White); g.DrawImageUnscaled(img, 0, 0); } Random rd = new Random(); string savepath = AppSettingsReader.UploadResourcePath + "/OutPutExcel/Temp/" + rd.Next() + ".jpg"; bmp.Save(savepath, System.Drawing.Imaging.ImageFormat.Jpeg); return savepath; } } #endregionCopy the codeIn this way, you can export Excel with images. Of course, you need to set a reasonable row height according to your own table, otherwise it is easy to distort.
At present, the most important thing is the speed problem. Once more than thousands of pictures are exported together, it will be slow. Later, we will slowly optimize it and talk about it after completing the task first. (Ps: programmers’ subsequent optimizations don’t know when…)