表格信息,导出图片"/>
NPOI导出表格信息,导出图片
StringBuilder strTemp = new StringBuilder();
strTemp.Append(@"select *,dbo.fu_GetCompanyNameById(company_id) as CompanyName from InvoiceSpecial where deleted=0");
// string compStr = ";
//站点,公司权限管理控制筛选
if (manager.role_id > 1)
{
if (manager.id > 0)
{
strTemp.AppendFormat(" and company_id={0}", manager.id);
}
}
strTemp.Append(" order by CreateDate desc");
DataTable dt = Query.ProcessSql(strTemp.ToString(), Common.PublicDAL.DbName);
if (!CommonClass.DTRow.CheckDtIsEmpty(dt))
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("发票下载");
//设置表列
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("发票图片"); //rowHeader.CreateCell(0, CellType.STRING).SetCellValue("发票图片");
row1.CreateCell(1).SetCellValue("公司名称");
row1.CreateCell(2).SetCellValue("发票类型");
row1.CreateCell(3).SetCellValue("验证状态");
row1.CreateCell(4).SetCellValue("开票日期");
row1.CreateCell(5).SetCellValue("价税合计");
row1.CreateCell(6).SetCellValue("不含税金额");
row1.CreateCell(7).SetCellValue("税率");
row1.CreateCell(8).SetCellValue("税额");
row1.CreateCell(9).SetCellValue("报销人");
row1.CreateCell(10).SetCellValue("发票代码");
row1.CreateCell(11).SetCellValue("发票号码");
row1.CreateCell(12).SetCellValue("费用类型");
row1.CreateCell(13).SetCellValue("关联单号");
row1.CreateCell(14).SetCellValue("关联单据状态");
row1.CreateCell(15).SetCellValue("销售方");
row1.CreateCell(16).SetCellValue("购买方");
row1.CreateCell(17).SetCellValue("备注");
//设置Head的样式
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
font.FontName = "Microsoft YaHei";
style.SetFont(font);//HEAD 样式
int last = row1.LastCellNum;
for (int i = 0; i < row1.LastCellNum; i++)
{
row1.GetCell(i).CellStyle = style;
}
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
sheet1.SetColumnWidth(0, 100 * 100);//设置单元格的宽度
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow hssfRow = sheet1.CreateRow(i + 1);
hssfRow.Height = 100 * 20; //设置单元格的高度
string picurl = dt.Rows[i]["item_image_url"].ToString();
//将图片文件读入一个字符串
setPic(book, patriarch, picurl, sheet1, i + 1, 0);
hssfRow.CreateCell(1).SetCellValue(dt.Rows[i]["CompanyName"].ToString());// row.CreateCell(0, CellType.STRING).SetCellValue(res[i].Name);
hssfRow.CreateCell(2).SetCellValue(dt.Rows[i]["invoice_type_msg"].ToString());
hssfRow.CreateCell(3).SetCellValue(dt.Rows[i]["is_verify"].ToString().ToLower() == "true" ? "已验票" : "未验票");
hssfRow.CreateCell(4).SetCellValue(dt.Rows[i]["date"].ToString());
hssfRow.CreateCell(5).SetCellValue(dt.Rows[i]["amount_little"].ToString());
hssfRow.CreateCell(6).SetCellValue(dt.Rows[i]["pretax_amount"].ToString());
hssfRow.CreateCell(7).SetCellValue(dt.Rows[i]["tax_rate"].ToString());
hssfRow.CreateCell(8).SetCellValue(dt.Rows[i]["tax_amount"].ToString());
hssfRow.CreateCell(9).SetCellValue(dt.Rows[i]["my_apply_man"].ToString());
hssfRow.CreateCell(10).SetCellValue(dt.Rows[i]["invoice_code"].ToString());
hssfRow.CreateCell(11).SetCellValue(dt.Rows[i]["invoice_no"].ToString());
hssfRow.CreateCell(12).SetCellValue(dt.Rows[i]["my_cost_type"].ToString());
hssfRow.CreateCell(13).SetCellValue(dt.Rows[i]["my_relate_no"].ToString());
hssfRow.CreateCell(14).SetCellValue(dt.Rows[i]["my_relate_status"].ToString());
hssfRow.CreateCell(15).SetCellValue(dt.Rows[i]["seller_name"].ToString());
hssfRow.CreateCell(16).SetCellValue(dt.Rows[i]["buyer_name"].ToString());
hssfRow.CreateCell(17).SetCellValue(dt.Rows[i]["my_remark"].ToString());
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
// 设置编码和附件格式
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyMMddHHmmssfff") + ".xls", Encoding.GetEncoding("gb2312")));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
private void setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, int rowline, int col)
{
if (string.IsNullOrEmpty(path)) return;
string imgPath = Utils.GetMapPath("/UploadFile/");
string FileName = imgPath + path.Replace("/", "");
if (File.Exists(FileName) == true)
{
byte[] bytes = System.IO.File.ReadAllBytes(FileName);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, col, rowline, col + 1, rowline + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
}
}
更多推荐
NPOI导出表格信息,导出图片
发布评论