using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
/// <summary>
/// 写数据到Excel
/// </summary>
/// <param name="dt">数据列表</param>
private void ExportResult(DataTable dt, string
colName)
{
string PICTURE_FILE_PATH = @"D:/IIS FOLDER
/web/files/templates/tmpJP.jpg";
try
{
if (null != dt)
{
HSSFWorkbook hssfworkbook;
//文件名称
string filename = "TWImage_" +
DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
Response.ContentType =
"application/vnd.ms-excel";
Response.AddHeader("Content-
Disposition", string.Format("attachment;filename=
{0}", filename));
Response.Clear();
hssfworkbook = new HSSFWorkbook();
HSSFSheet excelSheet = (HSSFSheet)
hssfworkbook.CreateSheet("Image_" +
DateTime.Now.ToString("yyyyMMdd"));//创建一个工作页
HSSFPatriarch patriarch =
(HSSFPatriarch)excelSheet.CreateDrawingPatriarch
();//每个Excel只能创建一个画板
excelSheet.SetColumnWidth(1, 15 *
256);//设置列宽
#region
Row rowHead = excelSheet.CreateRow
(0);
rowHead.CreateCell(0).SetCellValue
("品番");
rowHead.CreateCell(1).SetCellValue
("メイン画像");
#endregion
for (int i = 1; i < dt.Rows.Count +
1; i++)
{
Row row = excelSheet.CreateRow
(i);
excelSheet.GetRow(i).Height =
78 * 20;
#region
DataRow dtrow = dt.Rows[i - 1];
row.CreateCell(0).SetCellValue
(dtrow[colName].ToString());
#endregion
try
{
//第一个单元格添加图片
string strItemNo =
Convert.ToString(dtrow[colName]);
//if (ViewState
["numberall"] != null && ViewState
["numberall"].ToString().Equals(strItemNo))
//{
// continue;
//}
string ImgUrl = "";
//"http://www.dreamvs.jp/images/article/" +
strItemNo + "/" + strItemNo + "-01.jpg?x=100";//日
本服务器上图片地址
//ImgUrl = @"D:/ImgServer
/article_images/" + strItemNo + @"/" + strItemNo +
@"-01.jpg"; //改之前的+ @"m"
ImgUrl =
@"http://www.dreamvs.jp/images/article/" +
strItemNo + @"/" + strItemNo + @"-01.jpg";
//ImgUrl = @"/
/192.168.9.70/ImgServer/article_images/" +
strItemNo + @"/" + @"m" + strItemNo + @"-01.jpg";
//ImgUrl = @"/
/192.168.9.70/ImgServer/article_images/" +
strItemNo + @"/" + @"m" + strItemNo + @"-01.jpg";
System.Net.WebClient wc =
new System.Net.WebClient();
string Default_Img = @"D:
/IIS FOLDER/web/files/templates
/tmpShiyousyo.jpg";//改之前的
//string Default_Img = @"/
/192.168.9.70/ImgServer/article_images/" +
strItemNo + @"/" + @"m" + strItemNo + @"-01.jpg";
wc.DownloadFile(ImgUrl,
PICTURE_FILE_PATH);
wc.Dispose();
ImgUrl = PICTURE_FILE_PATH;
if (!File.Exists
(PICTURE_FILE_PATH))
{
ImgUrl = Default_Img;
}
HSSFClientAnchor anchor =
new HSSFClientAnchor(0, 0, 0, 0, 1, i, 2, i + 1);
HSSFPicture picture;
int picMain =
excelSheet.Workbook.AddPicture(File.ReadAllBytes
(ImgUrl), PictureType.JPEG);
picture = (HSSFPicture)
patriarch.CreatePicture(anchor, picMain);
//picture.LineStyle =
HSSFPicture.LINESTYLE_NONE;
//picture.Resize();//图片设
置为原始大小
if (ViewState["numberall"]
== null || !ViewState["numberall"].Equals
(strItemNo))
ViewState["numberall"]
= strItemNo;
}
catch { continue; }
}
ViewState["numberall"] = null;
MemoryStream file = new
MemoryStream();//1024*1024*100
//FileStream fs = new FileStream
(@"c:/temp.dat", FileMode.OpenOrCreate,
FileAccess.ReadWrite,FileShare.ReadWrite);
hssfworkbook.Write(file);
Response.BinaryWrite
(file.GetBuffer());
Response.End();
}
}
catch (Exception ex)
{
ComMethod.setMsg(1, "生成Excel异常(" +
ex.Message + ")", lblMessage);
}
finally
{
if (File.Exists(PICTURE_FILE_PATH))
{
File.Delete(PICTURE_FILE_PATH);
}
}
}