BarcodeLib.Barcode.dll+NPOI 下载和生成条形码导出excel

前端列表展示数据，显示图片：

        private void GetList()
        {
            int currPage = Utils.ReqIntParams("page", 1);
            int pageSize = Utils.ReqIntParams("rows", 30);
            int totalCount = 0;
            int totalPage = 0;
            string beginTime = Utils.ReqStrParams("bt", "");
            string endTime = Utils.ReqStrParams("et", "");
            string keyword = Utils.ReqStrParams("keyword", "-1");
            int provider = Utils.ReqIntParams("provider", -1);
            int totalSalesNum = 0;
            decimal totalSalesMoney = 0;
            DataTable dt = OrderDB.GetList(keyword, provider, beginTime, endTime,1, currPage, pageSize, ref totalCount, ref totalPage,ref totalSalesNum, ref totalSalesMoney);
            DataTable newDT = new DataTable();
            newDT.Columns.Add("rowno");
            newDT.Columns.Add("PNo");
            newDT.Columns.Add("PName");            
            newDT.Columns.Add("BarCode");
            newDT.Columns.Add("Proceeds");
            newDT.Columns.Add("Refund");
            newDT.Columns.Add("totalSalesNum");
            newDT.Columns.Add("totalSalesMoney");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = newDT.NewRow();
                dr["rowno"] = dt.Rows[i]["rowno"];
                dr["PNo"] = dt.Rows[i]["PNo"];
                dr["PName"] = dt.Rows[i]["PName"];
                dr["BarCode"] = dt.Rows[i]["BarCode"].ToString() != "" ? GenerateBarCodeBySpire(dt.Rows[i]["BarCode"].ToString()) : "";
                dr["Proceeds"] = dt.Rows[i]["Proceeds"];
                dr["Refund"] = dt.Rows[i]["Refund"];
                dr["totalSalesNum"] = dt.Rows[i]["totalSalesNum"];
                dr["totalSalesMoney"] = dt.Rows[i]["totalSalesMoney"];
                newDT.Rows.Add(dr);
            }
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("{");
            sb.Append("\"total\":");
            sb.Append("\"" + totalCount + "\",");
            sb.Append("\"totalSalesNum\":");
            sb.Append("\"" + totalSalesNum + "\",");
            sb.Append("\"totalSalesMoney\":");
            sb.Append("\"" + totalSalesMoney + "\",");
            newDT.TableName = "rows";
            string strjson = XpShopJson.DtToJSON(newDT);
            if (strjson.Trim() != "")
            {
                strjson = strjson.Remove(0, 1);
                strjson = strjson.Remove(strjson.Length - 1, 1);
            }
            sb.Append(strjson);
            sb.Append("}");
            Response.Write(sb.ToString());
        }

        /// <summary>
        /// 将条形编码转成Base64的图片路径
        /// </summary>
        /// <param name="codestr"></param>
        /// <returns></returns>
        private string GenerateBarCodeBySpire(string codestr)
        {
            BarcodeLib.Barcode.Linear barcode = new BarcodeLib.Barcode.Linear();

            barcode.Type = BarcodeType.CODE39;

            barcode.Data = codestr;

            barcode.UOM = UnitOfMeasure.PIXEL;
            barcode.BarWidth = 1;
            barcode.BarHeight = 80;
            barcode.LeftMargin = 5;
            barcode.RightMargin = 5;
            barcode.TopMargin = 5;
            barcode.BottomMargin = 5;

            barcode.ImageFormat = System.Drawing.Imaging.ImageFormat.Png;
            //Bitmap barcodeInBitmap = barcode.drawBarcode();

            MemoryStream ms = new MemoryStream();
            barcode.drawBarcode(ms);
            byte[] arr = new byte[ms.Length];
            ms.Position = 0;
            ms.Read(arr, 0, (int)ms.Length);
            ms.Close();
            string pic = Convert.ToBase64String(arr);
            return pic;
        }

 

导出excel

  1 private void ExportAll() {
  2             int currPage = Utils.ReqIntParams("page", 1);
  3             int pageSize = Utils.ReqIntParams("rows", 9999);
  4             int totalCount = 0;
  5             int totalPage = 0;
  6             string beginTime = Utils.ReqStrParams("bt", "");
  7             string endTime = Utils.ReqStrParams("et", "");
  8             string keyword = Utils.ReqStrParams("keyword", "-1");
  9             int provider = Utils.ReqIntParams("provider", -1);
 10             int totalSalesNum = 0;
 11             decimal totalSalesMoney = 0;
 12             DataTable dt = OrderDB.GetSalesStatistics(keyword, provider, beginTime, endTime, 2,currPage, pageSize, ref totalCount, ref totalPage, ref totalSalesNum, ref totalSalesMoney);
 13             string fileName = "销售统计数据-" + DateTime.Now.ToString("yyyyMMddHHmmss");
 14             if (dt.Rows.Count > 0)
 15             {
 16                 CreateXls(dt, totalSalesNum,totalSalesMoney,fileName);
 17             }
 18             else {
 19                 Utils.ShowMsg2(this, "没有可导出的数据！");
 20             }
 21         }
 22 
 23 public void CreateXls(DataTable dt,int totalSalesNum,decimal totalSalesMoney, string FileName)
 24         {
 25             string sheetName = "销售统计数据";
 26             //生成的表格的表头的主体
 27             List<string> liHeadSubject = new List<string>();
 28             liHeadSubject.Add("序号");
 29             liHeadSubject.Add("商品编号");
 30             liHeadSubject.Add("商品名称");
 31             liHeadSubject.Add("商品条码");
 32             liHeadSubject.Add("销售数量");
 33             liHeadSubject.Add("收款金额");
 34             liHeadSubject.Add("退款金额");            
 35             liHeadSubject.Add("销售金额");
 36             CreateXlsBT(dt,totalSalesNum,  totalSalesMoney, sheetName, liHeadSubject, Context, FileName);
 37         }
 38         
 39         /// <summary>
 40         /// 根据datatable数据内容，生成电子表格,这个是没有明细的
 41         /// </summary>
 42         /// <param name="dtContent">要导出的内容</param>
 43         /// <param name="sheetName"> 工作簿的名称</param>
 44         /// <param name="liHeadSubject">生成的表格的表头的主体，例如这个订单的主要数据，订单总价 收货人，收货地，手机，总购买数等</param>
 45         /// <param name="context"></param>
 46         /// <param name="fileName">生成的文件名</param>
 47         public  void CreateXlsBT(DataTable dtContent, int totalSalesNum, decimal totalSalesMoney, string sheetName, List<string> liHeadSubject, HttpContext context, string fileName)
 48         {
 49             //每个工作簿显示的数据行数//Excel2003版最大行数是65535行
 50             int sheetSize = 65535;
 51             //工作簿个数
 52             double sheetCount = Math.Ceiling(dtContent.Rows.Count * 1.00 / sheetSize);
 53 
 54             XSSFWorkbook book = new XSSFWorkbook();
 55             //创建工作簿
 56             ISheet se = book.CreateSheet(sheetName);
 57             //设置单元格高度
 58            // se.DefaultRowHeight = 35 * 38; //18 * 20;
 59             //创建行
 60             IRow row1 = se.CreateRow(0);
 61 
 62             ;
 63             ICell i0 = row1.CreateCell(0, CellType.String);
 64             i0.SetCellValue("");
 65             ICell i01 = row1.CreateCell(1, CellType.String);
 66             i01.SetCellValue("");
 67             ICell i02 = row1.CreateCell(2, CellType.String);
 68             i02.SetCellValue("");
 69             ICell i03 = row1.CreateCell(3, CellType.String);
 70             i03.SetCellValue("");
 71             ICell i04 = row1.CreateCell(4, CellType.String);
 72             i04.SetCellValue("销售总数量：" + totalSalesNum);
 73             ICell i05 = row1.CreateCell(5, CellType.String);
 74             i05.SetCellValue("");
 75             ICell i1 = row1.CreateCell(6, CellType.String);
 76             i1.SetCellValue("");
 77             ICell i2 = row1.CreateCell(7, CellType.String);
 78             i2.SetCellValue("销售总金额:"+totalSalesMoney.ToString());
 79 
 80             //4.创建CellStyle与DataFormat并加载格式样式
 81             IDataFormat dataformat = book.CreateDataFormat();
 82             ICellStyle style1 = book.CreateCellStyle();
 83             style1.DataFormat = dataformat.GetFormat("0.00");
 84             //创建行
 85             IRow row = se.CreateRow(1);
 86 
 87             //合并表头列
 88             List<string> liHead = new List<string>(liHeadSubject);
 89             int count = liHead.Count;
 90             for (int i = 0; i < count; i++)//创建表头
 91             {
 92                 //创建单元格
 93                 ICell ic = row.CreateCell(i, CellType.String);
 94                 ic.SetCellValue(liHead[i].Trim());
 95             }
 96             IRow onerow = se.GetRow(0);
 97             se.GetRow(1).Height= 22 * 24;
 98             onerow.Height = 22 * 24;
 99             int fillEmptyToColumnNum = liHeadSubject.Count;
100             for (int i = 0; i < dtContent.Rows.Count; i++)//创建内容
101             {
102                 row = se.CreateRow(i + 2);
103                 row.Height = 35 * 38;
104                 int columnsCount = dtContent.Columns.Count;
105                 for (int j = 0; j < columnsCount; j++)
106                 {
107                     ICell ic = row.CreateCell(j, CellType.String);
108 
109 
110                     if (j == 3)
111                     {//"data:image/png;base64," + dtContent.Rows[i][j].ToString().Trim()
112                         if (dtContent.Rows[i][j].ToString().Trim() != "")
113                         {
114                             //像excel插入图像
115                             AddCellPicture(se, book, GenerateBarCodeBySpireBitmap(dtContent.Rows[i][j].ToString()), i - 2, j);
116                         }
117 
118                     }
119                     else if (j == 4)
120                     {
121                         ic.SetCellValue((int)dtContent.Rows[i][j]);
122 
123                     } else if (j==7||j==6|| j == 5) {
124                         //ic.CellStyle = style1;
125                         ic.SetCellValue(double.Parse(dtContent.Rows[i][j].ToString().Trim()));
126                     }
127                     else {
128                         ic.SetCellValue(dtContent.Rows[i][j].ToString().Trim());
129 
130                     }
131                 }
132             }
133             se.SetColumnWidth(4, 20 * 256);
134             se.SetColumnWidth(5, 20 * 256);
135             se.SetColumnWidth(6, 20 * 256);
136             se.SetColumnWidth(7, 20 * 256);
137             //宽度自适应
138             for (int columnNum = 0; columnNum < count; columnNum++)
139             {
140                 int columnWidth = se.GetColumnWidth(columnNum) / 256;//获取当前列宽度  
141                 for (int rowNum = 1; rowNum <= se.LastRowNum; rowNum++)//在这一列上循环行  
142                 {
143                     IRow currentRow = se.GetRow(rowNum);
144                     ICell currentCell = currentRow.GetCell(columnNum);
145                     //int length = Encoding.UTF8.GetBytes(currentCell.ToString().Trim()).Length;//获取当前单元格的内容宽度  
146                     int length = currentCell.ToString().Length;//获取当前单元格的内容宽度
147                     if (columnWidth < length + 1)
148                     {
149                         columnWidth = length + 1;
150                     }//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符  
151                     
152                 }
153                 if (columnNum==3)
154                 {
155                     se.SetColumnWidth(columnNum, 16 * 840);
156                 }
157                 else { 
158                     se.SetColumnWidth(columnNum, columnWidth * 256);
159                 }
160             }
161 
162 
163             context.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" + fileName + ".xls;");
164             context.Response.ContentType = "application/vnd.ms-excel";
165             context.Response.ContentEncoding = System.Text.Encoding.UTF8;
166             book.Write(context.Response.OutputStream);
167             book.Close();
168             book = null;
169         }
170 
171 
172 /// <summary>
173         /// 将编码转成图形流
174         /// </summary>
175         /// <param name="codestr"></param>
176         /// <returns></returns>
177         private Bitmap GenerateBarCodeBySpireBitmap(string codestr)
178         {
179 
180             BarcodeLib.Barcode.Linear barcode = new BarcodeLib.Barcode.Linear();
181 
182             barcode.Type = BarcodeType.CODE39;
183 
184             barcode.Data = codestr;
185 
186             barcode.UOM = UnitOfMeasure.PIXEL;
187             barcode.BarWidth = 1;
188             barcode.BarHeight = 80;
189             barcode.LeftMargin = 5;
190             barcode.RightMargin = 5;
191             barcode.TopMargin = 5;
192             barcode.BottomMargin = 5;
193 
194             barcode.ImageFormat = System.Drawing.Imaging.ImageFormat.Png;
195             Bitmap barcodeInBitmap = barcode.drawBarcode();
196             return barcodeInBitmap;
197         }
198 
199 
200         /// <summary>
201         /// 向sheet插入图片
202         /// </summary> 
203         private static void AddCellPicture(ISheet sheet, XSSFWorkbook workbook, Bitmap b, int row, int col)
204         {
205             
206                 Bitmap bitmap = b; //读取图片流 
207                 Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中 
208                 bitmap.Dispose();   //将原来的图片流释放，将图片文件进行解锁。 
209 
210                 using (MemoryStream ms = new MemoryStream())
211                 {
212                     OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
213                     byte[] bytes = ms.ToArray();
214                    
215                     int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);  //添加图片 
216                     XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
217                     // XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row + 2, col + 1, row + 3);
218                     XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row+4 , col + 1, row + 5);
219                     //图片位置，图片左上角为（col, row） 
220                     XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
221                       //pict.Resize(); //用图片原始大小来显示   
222                     /*
223                     int widthPx = 300;
224                     int heightPx = 120;
225                     //设置列宽度，根据公式：POI中的列宽 ≈ 像素/8*256
226                     decimal width = Math.Round((decimal)(heightPx) / 8, 2);
227                     //将图片缩小为原来的十分之九
228                     decimal lessWidth = Math.Round(width * 9 / 10, 2);
229                     sheet.SetColumnWidth(col, Decimal.ToInt32(lessWidth * 256));
230                     IRow row3 = sheet.GetRow(row);
231                     //设置行高度，根据公式：POI中的行高 = 像素/DPI*72*20
232                     decimal poiHeight = Math.Round((decimal)(widthPx) / 7, 2);
233                     //将图片缩小为原来的十分之九
234                     decimal lessPoiHeight = Math.Round(poiHeight * 9 / 10, 2);
235                     row3.Height = (short)Decimal.ToInt32(lessPoiHeight * 72 * 20);
236                 */
237             }
238 
239         }

 

posted @ 2022-01-17 10:10  江小白ra  阅读(279)  评论(0)  编辑  收藏  举报