/// <summary>
/// 将datatable转化为Excel 导出图片
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public static string DataTableToExcel(List<Jaya.LearnToDrive.Model.S_COACH_RECEIPT> listModel, string tit, string JLID)
{
var filePath = HttpContext.Current.Server.MapPath("/Content/XLS/" + DateTime.Now.ToString("yyyy-MM-dd") + "/" + JLID + ".xls");
FileInfo fileif = new FileInfo(filePath);
if (!fileif.Directory.Exists) { fileif.Directory.Create(); }
using (var fileStream = new FileStream(filePath, FileMode.Create))
{
var workBook = new HSSFWorkbook();
var sheet = workBook.CreateSheet();
IRow headRow = sheet.CreateRow(0);
//设置标题
ICell cell = headRow.CreateCell(0);
cell.SetCellValue(tit);
if (listModel != null && listModel.Count > 0)
{
var lt_r = 1;//第一张图片所在行
var lt_c = 0;//第一张图片所在列
var br_r = 31;//第一张图片的右下角所在行
var br_c = 5;//第一张图片的右下角所在列
var i = 0;
var j = 0;
IDrawing patriarch = sheet.CreateDrawingPatriarch();
sheet.SetColumnWidth(0, 30 * 256);
foreach (var item in listModel)//遍历图片
{
if (!string.IsNullOrEmpty(item.CRT_ZP))
{
var tpath = HttpContext.Current.Server.MapPath(item.CRT_ZP);
if (File.Exists(tpath))
{
i++;
IRow newRow = sheet.CreateRow(i);
Bitmap pic = new Bitmap(tpath);
int width = pic.Size.Width; // 图片的宽度
int height = pic.Size.Height; // 图片的高度
// 一个12号字体的宽度为13,前面已设置了列的宽度为30*256,故这里的等比例高度计算如下
height = (int)Math.Round((height * (30 * 13) * 1.0 / width));
// excel单元格高度是以点单位,1点=2像素; POI中Height的单位是1/20个点,故设置单元的等比例高度如下
newRow.Height=((short)(height / 2 * 20));
byte[] bytes = System.IO.File.ReadAllBytes(tpath);
int pictureIndex = workBook.AddPicture(bytes, PictureType.PNG);
//处理图片位置,【图片左上角为(6, 2)第2+1行6+1列,右下角为(8, 6)第6+1行8+1列】
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short)j, i, (short)j, i );
//HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 6, 2, 8, 6);
//HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, lt_c, lt_r, br_c, br_r);
IPicture pict = patriarch.CreatePicture(anchor, pictureIndex);
//lt_r += 32;//与下一个图片间隔的行数
//br_r += 32;//与下一个图片间隔的行数
}
}
}
}
workBook.Write(fileStream);
workBook.Clear();
workBook.Close();
}
return filePath;
}