导出excel
/// <summary>
/// 增加二维码
/// </summary>
/// <param name="dt"></param>
/// <param name="isexcel"></param>
protected void AddPicture(DataTable dt, bool isexcel = true)
{
if (!Directory.Exists(imageBasePath))
{
Directory.CreateDirectory(imageBasePath);
}
if (!Directory.Exists(excelBasePath))
{
Directory.CreateDirectory(excelBasePath);
}
//创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
//create sheet
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("二维码打印");
string FileName ="二维码"+DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string strQrCodePath = "";
//填充列标题以及样式
int rowsNum = 0; //行号
// HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowsNum);
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = (HorizontalAlignment)HorizontalAlignment.Center;
//设置列宽,excel列宽每个像素是1/256
//设置行高 ,excel行高度每个像素点是1/20
// headerRow.HeightInPoints = 0; // 表头高度
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 16;
//font.Boldweight = 100;
// headStyle.SetFont(font);
//只需要一列 2016-3-24 宽度像素是1/256
var widths = (int)(0.5 * 256);
sheet.SetColumnWidth(0, 14*256);//*256 1厘米=96/2.54≈37.8像素。
sheet.SetColumnWidth(1, widths);
sheet.SetColumnWidth(2, 25 * 256);//*256 1厘米=96/2.54≈37.8像素。
//填充数据行
HSSFRow row = null;
rowsNum = 0;
int rowindex = 0;
//设置下初始值
int dx1 = 0, dy1 = 0, dx2 = 0, dy2 = 0, col1 = 0, row1 = rowindex, col2 = 0, row2 = rowindex;
string qrcodestr = "";
string msg = "";
int hight = 0;
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();//设置样式
HSSFFont cellfont = (HSSFFont)workbook.CreateFont(); //设置字体
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.VerticalAlignment = VerticalAlignment.Top;//对齐方式
cellStyle.WrapText = true;//设置换行这个要先设置
cellfont.FontHeightInPoints = 10;//字体大小
cellfont.Boldweight = 200;//字体厚度
foreach (DataRow dr in dt.Rows)
{
//循环列的次数 只要五列 先加第一行为空 高度是实在的像素
row = (HSSFRow)sheet.CreateRow(rowsNum);
//row.HeightInPoints = 47;
row.HeightInPoints = 102;//111(int)3.92 * 38; 148
row.CreateCell(0, CellType.String).SetCellValue("");
rowindex++;
rowsNum++;
for (int i = 0; i < 5; i++)
{
// cellfont = (HSSFFont)workbook.CreateFont();
row = (HSSFRow)sheet.CreateRow(rowsNum);
var cell = row.CreateCell(2, CellType.String);
//赋值都是第一列 0
if (dr[i]!=dr["二维码"])//只要不是二维码字符串就跳过
{
if (dr[i].Equals(dr["商品名称"]))
{
cellfont.FontHeightInPoints = 9;
cellfont.Boldweight =200;
row.HeightInPoints = 33.75f;
cellStyle.SetFont(cellfont);
cell.CellStyle = cellStyle;
cell.SetCellValue(dr[i].ToString());
}
if (dr[i].Equals(dr["产地"]))
{
cellfont.FontHeightInPoints = 12;
cellfont.Boldweight = 200;
row.HeightInPoints = 33.75f;
cellStyle.SetFont(cellfont);
cell.CellStyle = cellStyle;
cell.SetCellValue(dr[i].ToString());
}
if (dr[i].Equals(dr["规格"]))
{
cellfont.Boldweight = 200;
cellfont.FontHeightInPoints = 12;
row.HeightInPoints = 33.75f;
cellStyle.SetFont(cellfont);
cell.CellStyle = cellStyle;
cell.SetCellValue(dr[i].ToString());
}
if (dr[i].Equals(dr["清关类型"]))
{
cellfont.FontHeightInPoints = 13;
cellfont.Boldweight = 700;
row.HeightInPoints = 33.75f;
cellStyle.SetFont(cellfont);
cell.CellStyle = cellStyle;
cell.SetCellValue(dr[i].ToString());
}
}
else
{
row.HeightInPoints = 105;//高度是1/20
if (isexcel)
{
WebClient myWebClient = new WebClient();
string imagepathfix = imageBasePath + Guid.NewGuid().ToString();
string pfx = ".png";
if (dr[i].ToString().LastIndexOf(".jpg") > 0)
{
pfx = ".jpg";
}
else
{
}
string oldimagpath = imagepathfix + "1" + pfx;
strQrCodePath = imagepathfix + "." + pfx;
try
{
myWebClient.DownloadFile(dr[i].ToString(), oldimagpath);
}
catch (Exception exx)
{
msg += dr["商品名称"].ToString() + " 的二维码有问题," + exx.Message;
continue;
}
//处理下二维码大小
if (!string.IsNullOrEmpty(TextBox1.Text))
{
int.TryParse(TextBox1.Text.Trim(), out hight);
}
if (hight == 0)
hight = 102;
ImageClass.MakeThumbnail(oldimagpath, strQrCodePath, hight, hight, "H");
}
else
{
qrcodestr = "http://www.unions3.com/vshop/ProductDetails.aspx?productid="
+ dr[i].ToString();
strQrCodePath = GetQrCode(qrcodestr, false);
}
byte[] bytes = File.ReadAllBytes(strQrCodePath);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// Create the drawing patriarch. This is the top level container for all shapes.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
row1 = rowindex;
row2 = rowindex;
//add a picture
#region nopi创建画图
// 通常，利用NPOI画图主要有以下几个步骤：
//1.创建一个Patriarch；
//2.创建一个Anchor，以确定图形的位置；
//3.调用Patriarch创建图形；
//4.设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。
//关于HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)的参数，有必要在这里说明一下：
//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；
#endregion
//(int)3.07*38 (int)3.92*38
dx1 = 0; dy1 = 0; dx2 = (int)3.07 * 38; dy2 = (int)3.92 * 38; col1 = 1; row1 = rowindex; col2 = 1; row2 = rowindex;
HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
}
rowindex++;
rowsNum++;
}
//删除图片文件
if (File.Exists(strQrCodePath))
{
File.Delete(strQrCodePath);
}
}
//供浏览器下载Excel
if (HttpContext.Current.Request.Browser.Browser == "IE")
FileName = HttpUtility.UrlEncode(FileName);
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
ms.Close();
ms.Dispose();
// curContext.Response.End();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
//}
//catch (Exception ex)
//{
if (!string.IsNullOrEmpty(msg))
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('" + msg + "');</script>");
}
else
{
}
//}
}
/// <summary> /// 增加二维码 /// </summary> /// <param name="dt"></param> /// <param name="isexcel"></param> protected void AddPicture(DataTable dt, bool isexcel = true) {
if (!Directory.Exists(imageBasePath)) { Directory.CreateDirectory(imageBasePath); } if (!Directory.Exists(excelBasePath)) { Directory.CreateDirectory(excelBasePath); } //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //create sheet HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("二维码打印"); string FileName ="二维码"+DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"; string strQrCodePath = ""; //填充列标题以及样式 int rowsNum = 0; //行号 // HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowsNum); //HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); // headStyle.Alignment = (HorizontalAlignment)HorizontalAlignment.Center; //设置列宽,excel列宽每个像素是1/256 //设置行高 ,excel行高度每个像素点是1/20
// headerRow.HeightInPoints = 0; // 表头高度 //HSSFFont font = (HSSFFont)workbook.CreateFont(); //font.FontHeightInPoints = 16; //font.Boldweight = 100; // headStyle.SetFont(font); //只需要一列 2016-3-24 宽度像素是1/256 var widths = (int)(0.5 * 256); sheet.SetColumnWidth(0, 14*256);//*256 1厘米=96/2.54≈37.8像素。 sheet.SetColumnWidth(1, widths); sheet.SetColumnWidth(2, 25 * 256);//*256 1厘米=96/2.54≈37.8像素。 //填充数据行 HSSFRow row = null; rowsNum = 0; int rowindex = 0; //设置下初始值 int dx1 = 0, dy1 = 0, dx2 = 0, dy2 = 0, col1 = 0, row1 = rowindex, col2 = 0, row2 = rowindex; string qrcodestr = ""; string msg = ""; int hight = 0; HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();//设置样式 HSSFFont cellfont = (HSSFFont)workbook.CreateFont(); //设置字体 cellStyle.Alignment = HorizontalAlignment.Left; cellStyle.VerticalAlignment = VerticalAlignment.Top;//对齐方式 cellStyle.WrapText = true;//设置换行这个要先设置 cellfont.FontHeightInPoints = 10;//字体大小 cellfont.Boldweight = 200;//字体厚度 foreach (DataRow dr in dt.Rows) { //循环列的次数 只要五列 先加第一行为空 高度是实在的像素 row = (HSSFRow)sheet.CreateRow(rowsNum); //row.HeightInPoints = 47; row.HeightInPoints = 102;//111(int)3.92 * 38; 148 row.CreateCell(0, CellType.String).SetCellValue(""); rowindex++; rowsNum++; for (int i = 0; i < 5; i++) { // cellfont = (HSSFFont)workbook.CreateFont(); row = (HSSFRow)sheet.CreateRow(rowsNum); var cell = row.CreateCell(2, CellType.String); //赋值都是第一列 0 if (dr[i]!=dr["二维码"])//只要不是二维码字符串就跳过 { if (dr[i].Equals(dr["商品名称"])) { cellfont.FontHeightInPoints = 9; cellfont.Boldweight =200; row.HeightInPoints = 33.75f; cellStyle.SetFont(cellfont); cell.CellStyle = cellStyle; cell.SetCellValue(dr[i].ToString()); } if (dr[i].Equals(dr["产地"])) { cellfont.FontHeightInPoints = 12; cellfont.Boldweight = 200; row.HeightInPoints = 33.75f; cellStyle.SetFont(cellfont); cell.CellStyle = cellStyle; cell.SetCellValue(dr[i].ToString()); } if (dr[i].Equals(dr["规格"])) { cellfont.Boldweight = 200; cellfont.FontHeightInPoints = 12; row.HeightInPoints = 33.75f; cellStyle.SetFont(cellfont); cell.CellStyle = cellStyle; cell.SetCellValue(dr[i].ToString()); } if (dr[i].Equals(dr["清关类型"])) { cellfont.FontHeightInPoints = 13; cellfont.Boldweight = 700; row.HeightInPoints = 33.75f; cellStyle.SetFont(cellfont); cell.CellStyle = cellStyle; cell.SetCellValue(dr[i].ToString()); } } else { row.HeightInPoints = 105;//高度是1/20 if (isexcel) { WebClient myWebClient = new WebClient(); string imagepathfix = imageBasePath + Guid.NewGuid().ToString(); string pfx = ".png"; if (dr[i].ToString().LastIndexOf(".jpg") > 0) { pfx = ".jpg"; } else { } string oldimagpath = imagepathfix + "1" + pfx; strQrCodePath = imagepathfix + "." + pfx; try { myWebClient.DownloadFile(dr[i].ToString(), oldimagpath); } catch (Exception exx) { msg += dr["商品名称"].ToString() + " 的二维码有问题," + exx.Message; continue; } //处理下二维码大小 if (!string.IsNullOrEmpty(TextBox1.Text)) { int.TryParse(TextBox1.Text.Trim(), out hight); } if (hight == 0) hight = 102; ImageClass.MakeThumbnail(oldimagpath, strQrCodePath, hight, hight, "H"); } else { qrcodestr = "http://www.unions3.com/vshop/ProductDetails.aspx?productid=" + dr[i].ToString(); strQrCodePath = GetQrCode(qrcodestr, false); } byte[] bytes = File.ReadAllBytes(strQrCodePath); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // Create the drawing patriarch. This is the top level container for all shapes. HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); row1 = rowindex; row2 = rowindex; //add a picture #region nopi创建画图 // 通常，利用NPOI画图主要有以下几个步骤： //1.创建一个Patriarch； //2.创建一个Anchor，以确定图形的位置； //3.调用Patriarch创建图形； //4.设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。 //关于HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)的参数，有必要在这里说明一下： //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； #endregion //(int)3.07*38 (int)3.92*38 dx1 = 0; dy1 = 0; dx2 = (int)3.07 * 38; dy2 = (int)3.92 * 38; col1 = 1; row1 = rowindex; col2 = 1; row2 = rowindex; HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); } rowindex++; rowsNum++; }
//删除图片文件 if (File.Exists(strQrCodePath)) { File.Delete(strQrCodePath); } } //供浏览器下载Excel if (HttpContext.Current.Request.Browser.Browser == "IE") FileName = HttpUtility.UrlEncode(FileName); using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); ms.Close(); ms.Dispose(); // curContext.Response.End(); HttpContext.Current.ApplicationInstance.CompleteRequest(); } //} //catch (Exception ex) //{ if (!string.IsNullOrEmpty(msg)) { Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('" + msg + "');</script>"); } else
{
}
//} }