public byte[] GetCZTkshListOutList(JSReader jsr) { using (DBConnection dbc = new DBConnection()) { try { List <string> wArr = new List <string>(); string cx_beg = jsr["cx_beg"].ToString(); if (!string.IsNullOrEmpty(cx_beg)) { wArr.Add("a.addtime >= " + dbc.ToSqlValue(Convert.ToDateTime(cx_beg))); } string cx_endjsr = jsr["cx_end"].ToString(); if (!string.IsNullOrEmpty(cx_endjsr)) { wArr.Add("a.addtime < " + dbc.ToSqlValue(Convert.ToDateTime(cx_endjsr).AddDays(1))); } if (!string.IsNullOrEmpty(jsr["cx_fhrzh"])) { wArr.Add(dbc.C_Like("b.UserName", jsr["cx_fhrzh"], LikeStyle.LeftAndRightLike)); } if (!string.IsNullOrEmpty(jsr["cx_zxmc"])) { wArr.Add(dbc.C_Like("c.UserXM", jsr["cx_zxmc"], LikeStyle.LeftAndRightLike)); } if (!string.IsNullOrEmpty(jsr["cx_istk"])) { wArr.Add(dbc.C_EQ("a.status", Convert.ToInt32(jsr["cx_istk"].ToString()))); } string sqlW = ""; if (wArr.Count > 0) { sqlW = " and " + string.Join(" and ", wArr); } string str = @"select a.*,b.UserName fhrmc,c.UserXM zxmc ,d.addtime tktime,e.Money tkmoney, case when d.salerecordlx=0 then '耗材券' when d.salerecordlx=4 then '授权券' when d.salerecordlx=5 then '充值券' else '自发布券' end as qlx from tb_b_myvoucher a left join tb_b_user b on a.UserID=b.UserID left join tb_b_user c on a.CardUserID=c.UserID left join tb_b_refund d on a.myvoucherid=d.mycardid left join tb_b_order e on a.OrderCode=e.OrderCode where a.status in(2,3) and a.OrderCode is not null "; str += sqlW; //开始取分页数据 System.Data.DataTable dt = dbc.ExecuteDataTable(str + " order by a.status asc,a.addtime desc"); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 //第一行标题列表 //合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行,参数0代表当前行当前列即第一行第一列,参数3合并的行数,参数4合并的列数 cells.Merge(0, 0, 1, 9); cells[0, 0].PutValue("充值退款数据导出"); cells[0, 0].SetStyle(style3); cells.SetRowHeight(0, 30); cells.SetColumnWidth(0, 30); cells[2, 0].PutValue("订单号"); cells[2, 0].SetStyle(style1); cells.SetColumnWidth(1, 15); cells[2, 1].PutValue("申请退款券额"); cells[2, 1].SetStyle(style1); cells.SetColumnWidth(2, 30); cells[2, 2].PutValue("发货人账号"); cells[2, 2].SetStyle(style1); cells.SetColumnWidth(3, 30); cells[2, 3].PutValue("专线名称"); cells[2, 3].SetStyle(style1); cells.SetColumnWidth(4, 15); cells[2, 4].PutValue("购买时间"); cells[2, 4].SetStyle(style1); cells.SetColumnWidth(5, 20); cells[2, 5].PutValue("退款状态"); cells[2, 5].SetStyle(style1); cells.SetColumnWidth(6, 20); cells[2, 6].PutValue("退款时间"); cells[2, 6].SetStyle(style1); cells.SetColumnWidth(7, 20); cells[2, 7].PutValue("退款金额"); cells[2, 7].SetStyle(style1); cells.SetColumnWidth(8, 20); cells[2, 8].PutValue("券类型"); cells[2, 8].SetStyle(style1); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { var status = ""; if (dt.Rows[i]["status"] != null && dt.Rows[i]["status"].ToString() != "") { if (dt.Rows[i]["status"].ToString() == "2") { status = "退款审核中"; } else if (dt.Rows[i]["status"].ToString() == "3") { status = "已退款"; } } cells[i + 3, 0].PutValue(dt.Rows[i]["OrderCode"].ToString()); cells[i + 3, 0].SetStyle(style2); cells[i + 3, 1].PutValue(dt.Rows[i]["points"].ToString()); cells[i + 3, 1].SetStyle(style2); cells[i + 3, 2].PutValue(dt.Rows[i]["fhrmc"].ToString()); cells[i + 3, 2].SetStyle(style2); cells[i + 3, 3].PutValue(dt.Rows[i]["zxmc"].ToString()); cells[i + 3, 3].SetStyle(style2); if (dt.Rows[i]["addtime"].ToString() != "") { cells[i + 3, 4].PutValue(Convert.ToDateTime(dt.Rows[i]["addtime"].ToString()).ToString("yyyy-MM-dd")); } else { cells[i + 3, 4].PutValue(""); } cells[i + 3, 4].SetStyle(style2); cells[i + 3, 5].PutValue(status); cells[i + 3, 5].SetStyle(style2); if (dt.Rows[i]["tktime"].ToString() != "") { cells[i + 3, 6].PutValue(Convert.ToDateTime(dt.Rows[i]["tktime"].ToString()).ToString("yyyy-MM-dd")); } else { cells[i + 3, 6].PutValue(""); } cells[i + 3, 6].SetStyle(style2); cells[i + 3, 7].PutValue(dt.Rows[i]["tkmoney"].ToString()); cells[i + 3, 7].SetStyle(style2); cells[i + 3, 8].PutValue(dt.Rows[i]["qlx"].ToString()); cells[i + 3, 8].SetStyle(style2); } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public static bool DataTableToExcel(DataTable datatable, System.Web.HttpResponse response, out string error) { error = ""; try { string fileName = DateTime.Now.ToString("yyyyMMdd_hhMMssfff") + ".xls"; if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; foreach (DataRow row in datatable.Rows) { nRow++; try { for (int i = 0; i < datatable.Columns.Count; i++) { if (row[i].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[i]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, i, mstream); } else { cells[nRow, i].PutValue(row[i]); } } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } response.Clear(); response.Buffer = true; response.Charset = "utf-8"; response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); response.ContentEncoding = System.Text.Encoding.UTF8; response.ContentType = "application/ms-excel"; response.BinaryWrite(workbook.SaveToStream().ToArray()); response.End(); //workbook.Save(filepath); return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
public byte[] ExportSourceGoods(string stime, string etime, string username, string ddbm, string ishx) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string where = ""; if (!string.IsNullOrEmpty(username)) { where += " and " + dbc.C_Like("f.username", username.Trim(), LikeStyle.LeftAndRightLike); } if (stime != null && stime != "") { where += " and c.shippingnoteadddatetime>=" + dbc.ToSqlValue(stime); } if (etime != null && etime != "") { where += " and c.shippingnoteadddatetime<=" + dbc.ToSqlValue(etime); } if (ddbm != null && ddbm != "") { where += " and c.shippingnotenumber like '%" + ddbm + "%'"; } string str = @" select *,(money - hxmoney) symoney from ( SELECT g.id as costid, h.username AS um,g.money,case when g.verifymoney is not null then g.verifymoney else 0 end AS hxmoney,e.descriptionofgoods,e.itemgrossweight,e.goodsfromroute,e.goodstoroute,e.goodsreceiptplace, f.vehiclenumber,f.vehicletype,f.identitydocumentnumber, c.offerid,(SELECT SUM(verifymoney) FROM tb_b_shippingnoteinfo_verify WHERE costid=g.id) AS verifymoney,e.actualmoney,e.actualdrivermoney,f.username,f.userid,c.shippingnoteid,c.shippingnoteadddatetime,c.shippingnotenumber,c.statisticstype,d.totalamount,d.totalvaloremtax,d.rate,d.billingtime,d.invoicecode,d.invoicenumber FROM tb_b_shippingnoteinfo c LEFT JOIN (SELECT a.shippingnoteid,b.totalvaloremtax,b.rate,b.billingtime,b.invoicecode,b.invoicenumber,b.totalamount FROM tb_b_invoicedetail a LEFT JOIN tb_b_invoice b ON a.billingid=b.billingid ) d ON c.shippingnoteid=d.shippingnoteid LEFT JOIN tb_b_sourcegoodsinfo_offer e ON c.offerid=e.offerid INNER JOIN tb_b_user h ON e.shipperid=h.userid LEFT JOIN tb_b_shippingnoteinfo_cost g ON c.shippingnoteid=g.shippingnoteid AND g.status = 0 AND g.usertype = 4 INNER JOIN tb_b_user f ON g.userid=f.userid AND f.usertype=4 WHERE c.shippingnotestatuscode >= 30 and c.isdeleteflag = 0 "; str += where + " ORDER BY c.consignmentdatetime) AS tab "; if (ishx != null && ishx != "") { if (ishx == "0") { str += " where (hxmoney=0 or hxmoney is null)"; } else if (ishx == "1") { str += " where hxmoney<money "; } else if (ishx == "2") { str += " where hxmoney=money "; } } //开始取分页数据 System.Data.DataTable dt = new System.Data.DataTable(); dt = dbc.ExecuteDataTable(str); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 #region 样式 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 #endregion String[] ColumnName = { "订单时间", "厂家", "单号", "起始地", "目的地", "收货地址", "货物", "司机名称", "车牌", "车型", "身份证", "司机金额", "司机总付款", "已核销金额", "剩余可核销金额" }; String[] ColumnV = { "shippingnoteadddatetime", "um", "shippingnotenumber", "goodsfromroute", "goodstoroute", "goodsreceiptplace", "descriptionofgoods", "username", "vehiclenumber", "vehicletype", "identitydocumentnumber", "actualdrivermoney", "money", "verifymoney", "symoney" }; for (int i = 0; i < ColumnName.Length; i++) { cells.SetColumnWidth(i, 30); cells[0, i].PutValue(ColumnName[i]); cells[0, i].SetStyle(style1); } if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { for (int a = 0; a < ColumnV.Length; a++) { var ColumnVs = dt.Rows[i][ColumnV[a]].ToString(); cells[i + 1, a].PutValue(ColumnVs); cells[i + 1, a].SetStyle(style2); } } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public byte[] GetTkshListOutListV3(JSReader jsr) { using (DBConnection dbc = new DBConnection()) { try { var cmd = dbc.CreateCommand(); cmd.CommandText = @" select e.dq_mc,a.OrderCode,a.AddTime,a.Points,b.UserName,c.UserXM from tb_b_order a left join tb_b_user b on a.BuyUserID = b.UserID left join tb_b_user c on a.SaleUserID = c.UserID left join tb_b_dq e on c.DqBm=e.dq_bm left join tb_b_plattosale f on f.PlatToSaleId=a.PlatToSaleId where a.status = 0 and a.zhifuzt = 1 "; if (jsr["beg"] != null && jsr["beg"].ToString() != "") { cmd.CommandText += " and a.addtime >=" + dbc.ToSqlValue(Convert.ToDateTime(jsr["beg"].ToString())); } if (jsr["end"] != null && jsr["end"].ToString() != "") { cmd.CommandText += " and a.addtime <=" + dbc.ToSqlValue(Convert.ToDateTime(jsr["end"].ToString())); } if (jsr["areacode"] != null && jsr["areacode"].ToString() != "") { cmd.CommandText += " and c.DqBm =" + dbc.ToSqlValue(jsr["areacode"].ToString()); } else { cmd.CommandText += " and ( c.DqBm='320500' or c.DqBm='320400')"; } cmd.CommandText += @" and c.isdonate = 0 and f.pointkind = 4 and f.SaleRecordVerifyType = 1 and f.status = 0"; cmd.CommandText += " and ordercode not in (select ordercode from tb_b_redenvelope where type = 5 "; if (jsr["beg"] != null && jsr["beg"].ToString() != "") { cmd.CommandText += " and addtime >=" + dbc.ToSqlValue(Convert.ToDateTime(jsr["beg"].ToString())); } if (jsr["end"] != null && jsr["end"].ToString() != "") { cmd.CommandText += " and addtime <=" + dbc.ToSqlValue(Convert.ToDateTime(jsr["end"].ToString())); } cmd.CommandText += ") order by a.addtime desc"; System.Data.DataTable dt = dbc.ExecuteDataTable(cmd); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = System.Drawing.Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 //第一行标题列表 //合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行,参数0代表当前行当前列即第一行第一列,参数3合并的行数,参数4合并的列数 cells.Merge(0, 0, 1, 6); cells[0, 0].PutValue("历史原价购买订单数"); cells[0, 0].SetStyle(style3); cells.SetRowHeight(0, 30); cells.SetColumnWidth(0, 30); cells[2, 0].PutValue("地区"); cells[2, 0].SetStyle(style1); cells.SetColumnWidth(1, 30); cells[2, 1].PutValue("订单号"); cells[2, 1].SetStyle(style1); cells.SetColumnWidth(2, 30); cells[2, 2].PutValue("时间"); cells[2, 2].SetStyle(style1); cells.SetColumnWidth(3, 30); cells[2, 3].PutValue("消费券额"); cells[2, 3].SetStyle(style1); cells.SetColumnWidth(4, 30); cells[2, 4].PutValue("用户"); cells[2, 4].SetStyle(style1); cells.SetColumnWidth(5, 50); cells[2, 5].PutValue("专线名称"); cells[2, 5].SetStyle(style1); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 3, 0].PutValue(dt.Rows[i]["dq_mc"].ToString()); cells[i + 3, 0].SetStyle(style2); cells[i + 3, 1].PutValue(dt.Rows[i]["OrderCode"].ToString()); cells[i + 3, 1].SetStyle(style2); cells[i + 3, 2].PutValue(Convert.ToDateTime(dt.Rows[i]["AddTime"].ToString())); cells[i + 3, 2].SetStyle(style2); cells[i + 3, 3].PutValue(dt.Rows[i]["Points"].ToString()); cells[i + 3, 3].SetStyle(style2); cells[i + 3, 4].PutValue(dt.Rows[i]["UserName"].ToString()); cells[i + 3, 4].SetStyle(style2); cells[i + 3, 5].PutValue(dt.Rows[i]["UserXM"].ToString()); cells[i + 3, 5].SetStyle(style2); } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
/// <summary> /// 导出Excel,新建SheetName /// </summary> /// <param name="dtData">数据List,</param> /// <param name="SheetName">SheetName,名称少于40个字符,如果为空默认Sheet1,条数超过65535,自动新建SheetName1</param> /// <param name="Exportpro">Excel列字段,不设置全部导出</param> /// <param name="ExcelName">下载Excel显示名称</param> /// <param name="HttpContext">本页面HttpContext变量</param> public static void DataToExcel <T>(List <T> dtData, String SheetName, List <string> Exportpro, string ExcelName, System.Web.HttpContextBase HttpContext) where T : new() { string sheetName = SheetName; if (sheetName.Trim() == string.Empty) { sheetName = "Sheet1"; } if (Exportpro == null) { Exportpro = new List <string>(); } if (sheetName.Length > 40) { sheetName = sheetName.Substring(0, 40); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();//定义导出的Excel对象 workbook.Worksheets.Clear(); for (int i = 0; i <= dtData.Count / 65536; i++)//根据记录条数,创建不同的Sheet,以便兼容 excel 2003。 { if (i == 0) { workbook.Worksheets.Add(sheetName); workbook.Worksheets[0].AutoFitColumns(); } else { workbook.Worksheets.Add(sheetName + i.ToString()); workbook.Worksheets[i].AutoFitColumns(); } } #region 标题样式 //为单元格添加样式 Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; //设置居中 style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //设置背景颜色 style.ForegroundColor = System.Drawing.Color.FromArgb(192, 192, 192); style.Pattern = BackgroundType.Solid; style.Font.IsBold = true; style.Font.Name = "黑体"; style.Borders[BorderType.BottomBorder].LineStyle = (CellBorderType.Thin); style.Borders[BorderType.TopBorder].LineStyle = (CellBorderType.Thin); style.Borders[BorderType.RightBorder].LineStyle = (CellBorderType.Thin); style.Borders[BorderType.LeftBorder].LineStyle = (CellBorderType.Thin); style.Borders.SetColor(System.Drawing.Color.Black); #endregion #region 内容样式 //为单元格添加样式 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //设置居中 style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; style1.Pattern = BackgroundType.Solid; style1.Font.Name = "宋体"; style1.Borders[BorderType.BottomBorder].LineStyle = (CellBorderType.Thin); style1.Borders[BorderType.TopBorder].LineStyle = (CellBorderType.Thin); style1.Borders[BorderType.RightBorder].LineStyle = (CellBorderType.Thin); style1.Borders[BorderType.LeftBorder].LineStyle = (CellBorderType.Thin); style1.Borders.SetColor(System.Drawing.Color.Black); #endregion T t0 = new T(); int i0 = 0; int j = 0; int sheetindex = 0; int rowid = 1; PropertyInfo[] propertyInfos = t0.GetType().GetProperties();// if (Exportpro.Count > 0) { foreach (string n in Exportpro) { string headname = GetModelMetadata <T>(n).DisplayName; foreach (Aspose.Cells.Worksheet sheet in workbook.Worksheets) { sheet.Cells[0, i0].PutValue(headname); sheet.Cells[0, i0].SetStyle(style); sheet.Cells.SetRowHeight(0, 30); sheet.Cells.SetColumnWidth(i0, 30); } i0++; } foreach (T t in dtData) { i0 = 0; foreach (string n in Exportpro) { object obj = t.GetType().GetProperties().SingleOrDefault(model => model.Name == n).GetValue(t, null); if (obj == null) { obj = ""; } sheetindex = j / 65535; rowid = j - sheetindex * 65535 + 1; workbook.Worksheets[sheetindex].Cells[rowid, i0].PutValue(obj.ToString()); workbook.Worksheets[sheetindex].Cells[rowid, i0].SetStyle(style1); workbook.Worksheets[sheetindex].Cells.SetRowHeight(rowid, 30); i0++; } workbook.Worksheets[sheetindex].AutoFitColumns(); j++; } } else { foreach (PropertyInfo propertyInfo in propertyInfos) { string headname = GetModelMetadata <T>(propertyInfo.Name).DisplayName; foreach (Aspose.Cells.Worksheet sheet in workbook.Worksheets) { sheet.Cells[0, i0].PutValue(headname); sheet.Cells[0, i0].SetStyle(style); sheet.Cells.SetColumnWidth(i0, 30); sheet.Cells.SetRowHeight(0, 30); } i0++; } foreach (T t in dtData) { i0 = 0; foreach (PropertyInfo propertyInfo in propertyInfos) { object obj = t.GetType().GetProperties().SingleOrDefault(model => model.Name == propertyInfo.Name).GetValue(t, null); if (obj == null) { obj = ""; } sheetindex = j / 65535; rowid = j - sheetindex * 65535 + 1; workbook.Worksheets[sheetindex].Cells[rowid, i0].PutValue(obj.ToString()); workbook.Worksheets[sheetindex].Cells[rowid, i0].SetStyle(style1); workbook.Worksheets[sheetindex].Cells.SetRowHeight(rowid, 30); i0++; } workbook.Worksheets[sheetindex].AutoFitColumns(); j++; } } MemoryStream fileStream = workbook.SaveToStream(); if (HttpContext.Request.UserAgent.ToLower().IndexOf("msie") > -1) { ExcelName = ToHexString(ExcelName); } if (HttpContext.Request.UserAgent.ToLower().IndexOf("firefox") > -1) { HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + ExcelName + ".xls\""); } else { HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls"); } HttpContext.Response.AddHeader("Content-length", fileStream.Length.ToString()); HttpContext.Response.BinaryWrite(fileStream.GetBuffer()); HttpContext.Response.Flush(); fileStream.Dispose(); HttpContext.Response.End(); }
public byte[] GetTkshListOutList(JSReader jsr) { using (DBConnection dbc = new DBConnection()) { try { List <string> wArr = new List <string>(); string cx_beg = jsr["cx_beg"].ToString(); if (!string.IsNullOrEmpty(cx_beg)) { wArr.Add("a.addtime >= " + dbc.ToSqlValue(Convert.ToDateTime(cx_beg))); } string cx_endjsr = jsr["cx_end"].ToString(); if (!string.IsNullOrEmpty(cx_endjsr)) { wArr.Add("a.addtime < " + dbc.ToSqlValue(Convert.ToDateTime(cx_endjsr).AddDays(1))); } if (!string.IsNullOrEmpty(jsr["cx_userid"])) { wArr.Add(dbc.C_Like("b.UserName", jsr["cx_userid"], LikeStyle.LeftAndRightLike)); } if (!string.IsNullOrEmpty(jsr["cx_typee"])) { wArr.Add(dbc.C_Like("a.type", jsr["cx_typee"], LikeStyle.LeftAndRightLike)); } string sqlW = ""; if (wArr.Count > 0) { sqlW = " and " + string.Join(" and ", wArr); } string str = @"select b.UserName,case when a.type=1 then '行驶证查询类型' when a.type=0 then 'gps查询类型' end as typeName ,a.addtime,a.carnumber from tb_b_zjxl_search a left join tb_b_user b on a.userid=b.UserID where 1=1 "; str += sqlW; //开始取分页数据 System.Data.DataTable dt = dbc.ExecuteDataTable(str + " order by a.status asc,a.addtime desc"); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = System.Drawing.Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 //第一行标题列表 //合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行,参数0代表当前行当前列即第一行第一列,参数3合并的行数,参数4合并的列数 cells.Merge(0, 0, 1, 4); cells[0, 0].PutValue("中交兴路查询统计数据"); cells[0, 0].SetStyle(style3); cells.SetRowHeight(0, 30); cells.SetColumnWidth(0, 30); cells[2, 0].PutValue("账号"); cells[2, 0].SetStyle(style1); cells.SetColumnWidth(1, 30); cells[2, 1].PutValue("查询类型"); cells[2, 1].SetStyle(style1); cells.SetColumnWidth(2, 30); cells[2, 2].PutValue("查询时间"); cells[2, 2].SetStyle(style1); cells.SetColumnWidth(3, 30); cells[2, 3].PutValue("查询内容"); cells[2, 3].SetStyle(style1); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 3, 0].PutValue(dt.Rows[i]["UserName"].ToString()); cells[i + 3, 0].SetStyle(style2); cells[i + 3, 1].PutValue(dt.Rows[i]["typeName"].ToString()); cells[i + 3, 1].SetStyle(style2); cells[i + 3, 2].PutValue(Convert.ToDateTime(dt.Rows[i]["addtime"].ToString()).ToString("yyyy-MM-dd")); cells[i + 3, 2].SetStyle(style2); cells[i + 3, 3].PutValue(dt.Rows[i]["carnumber"].ToString()); cells[i + 3, 3].SetStyle(style2); } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public byte[] GetTkshListOutList(string areacode) { using (DBConnection dbc = new DBConnection()) { try { var cmd = dbc.CreateCommand(); cmd.CommandText = @"select c.dq_mc,b.userxm,a.addtime,a.quota from tb_b_user_quota a left join tb_b_user b on a.userid = b.UserID left join tb_b_dq c on b.DqBm=c.dq_bm where a.status = 0and b.isdonate = 0"; if (areacode != null && areacode != "") { cmd.CommandText += " and b.DqBm =" + dbc.ToSqlValue(areacode); } else { cmd.CommandText += " and ( b.DqBm='320500' or b.DqBm='320400')"; } cmd.CommandText += "order by a.addtime desc"; System.Data.DataTable dt = dbc.ExecuteDataTable(cmd); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = System.Drawing.Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 //第一行标题列表 //合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行,参数0代表当前行当前列即第一行第一列,参数3合并的行数,参数4合并的列数 cells.Merge(0, 0, 1, 4); cells[0, 0].PutValue("后台配比红包总额度明细"); cells[0, 0].SetStyle(style3); cells.SetRowHeight(0, 30); cells.SetColumnWidth(0, 30); cells[2, 0].PutValue("地区"); cells[2, 0].SetStyle(style1); cells.SetColumnWidth(1, 30); cells[2, 1].PutValue("专线名称"); cells[2, 1].SetStyle(style1); cells.SetColumnWidth(2, 30); cells[2, 2].PutValue("查询时间"); cells[2, 2].SetStyle(style1); cells.SetColumnWidth(3, 30); cells[2, 3].PutValue("总额度"); cells[2, 3].SetStyle(style1); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 3, 0].PutValue(dt.Rows[i]["dq_mc"].ToString()); cells[i + 3, 0].SetStyle(style2); cells[i + 3, 1].PutValue(dt.Rows[i]["userxm"].ToString()); cells[i + 3, 1].SetStyle(style2); cells[i + 3, 2].PutValue(Convert.ToDateTime(dt.Rows[i]["addtime"].ToString()).ToString("yyyy-MM-dd")); cells[i + 3, 2].SetStyle(style2); cells[i + 3, 3].PutValue(dt.Rows[i]["quota"].ToString()); cells[i + 3, 3].SetStyle(style2); } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public byte[] ExportGYSHX(string stime, string etime, string username, string ddbm) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string where = ""; if (!string.IsNullOrEmpty(username)) { where += " and " + dbc.C_Like("f.username", username.Trim(), LikeStyle.LeftAndRightLike); } if (stime != null && stime != "") { where += " and c.shippingnoteadddatetim>=" + dbc.ToSqlValue(stime); } if (etime != null && etime != "") { where += " and c.shippingnoteadddatetim<=" + dbc.ToSqlValue(etime); } if (ddbm != null && ddbm != "") { where += " and c.shippingnotenumber like '%" + ddbm + "%'"; } string str = @" SELECT e.actualcompanypay,e.goodsfromroute,e.goodstoroute,e.goodsreceiptplace,e.descriptionofgoods,d.invoicestatus,d.billingid,e.actualmoney,f.username,f.userid,c.shippingnoteid,c.shippingnoteadddatetime,c.shippingnotenumber,c.statisticstype,d.totalamount,d.totalvaloremtax,d.rate,d.billingtime,d.invoicecode,d.invoicenumber FROM tb_b_shippingnoteinfo c LEFT JOIN (SELECT a.shippingnoteid,b.totalvaloremtax,b.rate,b.billingtime,b.invoicecode,b.invoicenumber,b.totalamount,b.invoicestatus,b.billingid FROM tb_b_invoicedetail a LEFT JOIN tb_b_invoice b ON a.billingid=b.billingid ) d ON c.shippingnoteid=d.shippingnoteid LEFT JOIN tb_b_sourcegoodsinfo_offer e ON c.offerid=e.offerid LEFT JOIN tb_b_user f ON e.shipperid=f.userid WHERE (d.invoicestatus=0 OR c.shippingnoteid NOT IN (SELECT shippingnoteid FROM tb_b_invoicedetail ) ) AND c.shippingnotestatuscode = 90 "; str += where + " ORDER BY c.consignmentdatetime"; //开始取分页数据 System.Data.DataTable dt = new System.Data.DataTable(); dt = dbc.ExecuteDataTable(str); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 #region 样式 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 #endregion String[] ColumnName = { "订单时间", "厂家", "单号", "起始地", "目的地", "收货地址", "货物", "应收金额", "合计金额", "开票时间", "发票代码", "发票号码" }; String[] ColumnV = { "shippingnoteadddatetime", "username", "shippingnotenumber", "goodsfromroute", "goodstoroute", "goodsreceiptplace", "descriptionofgoods", "actualcompanypay", "totalamount", "billingtime", "invoicecode", "invoicenumber" }; for (int i = 0; i < ColumnName.Length; i++) { cells.SetColumnWidth(i, 30); cells[0, i].PutValue(ColumnName[i]); cells[0, i].SetStyle(style1); } if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { for (int a = 0; a < ColumnV.Length; a++) { var ColumnVs = dt.Rows[i][ColumnV[a]].ToString(); cells[i + 1, a].PutValue(ColumnVs); cells[i + 1, a].SetStyle(style2); } } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
/// <summary> /// Aspose批量Sheet导出 /// </summary> /// <param name="dtSource">多个sheet</param> /// <param name="HeadTitle">标题</param> /// <param name="HeadTitletow">二标题</param> /// <param name="pageName">每个sheet名称</param> /// <returns></returns> public static MemoryStream ExportByAspose(List <DataTable> dtSource, List <string> HeadTitle, List <string> HeadTitletow, List <string> pageName) { Workbook workbook = new Aspose.Cells.Workbook(); //清除页先 要不然 新建就有一个sheet workbook.Worksheets.Clear(); #region 样式 //标题样式 Style styleTitle = workbook.Styles[workbook.Styles.Add()]; styleTitle.HorizontalAlignment = TextAlignmentType.Center; styleTitle.Font.Name = "宋体"; styleTitle.Font.Size = 18; styleTitle.Font.IsBold = true; //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Right; style2.Font.IsBold = false; style2.IsTextWrapped = true; //列样式 Style styleColumn = workbook.Styles[workbook.Styles.Add()]; styleColumn.HorizontalAlignment = TextAlignmentType.Left; styleColumn.VerticalAlignment = TextAlignmentType.Center; styleColumn.Font.IsBold = true; styleColumn.Font.IsNormalizeHeights = true; #endregion for (int i = 0; i < dtSource.Count; i++) { string sheetName = pageName[i].Trim(); sheetName = sheetName.Length > 31 ? sheetName.Substring(0, 31) : sheetName; //创建sheet(excel限制最大长度为31) workbook.Worksheets.Add(sheetName); //获取当前sheet Worksheet sheet = workbook.Worksheets[i]; Cells cells = sheet.Cells; int rowIndex = 0; if (dtSource[i] != null) { //标题行 int colNum = dtSource[i].Columns.Count; //表格列数 int rowNum = dtSource[i].Rows.Count; //表格行数 cells.Merge(0, 0, 1, colNum); //合并单元格//生成行1 标题行 cells[0, 0].PutValue(HeadTitle[i].ToString().Trim()); //填写标题内容 cells[0, 0].SetStyle(styleTitle); //标题样式 cells.SetRowHeight(0, 30); //设置行高 //标题第二行 cells.Merge(1, 0, 1, colNum);//合并单元格 cells[1, 0].PutValue(HeadTitletow[i] + "| 导出时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm")); cells[1, 0].SetStyle(style2); cells.SetRowHeight(1, 18); //列名行 for (int ct = 0; ct < colNum; ct++) { cells[2, ct].PutValue(dtSource[i].Columns[ct].ColumnName); cells[2, ct].SetStyle(styleColumn); } cells.SetRowHeight(2, 18); rowIndex = 3; //生成数据行 for (int r = 0; r < rowNum; r++) { //数据列遍历 for (int c = 0; c < colNum; c++) { string drValue = dtSource[i].Rows[r][c].ToString(); double doubV = 0; //设置单元格数据根据值类型转换 if (double.TryParse(drValue, out doubV)) { cells[rowIndex, c].PutValue(doubV); } else { cells[rowIndex, c].PutValue(drValue); } } cells.SetRowHeight(rowIndex, 18); rowIndex++; } } //sheet.AutoFitColumns();//自动填充列宽(不完美) setColumnWithAuto(sheet); } MemoryStream ms = workbook.SaveToStream(); return(ms); }
protected void btnExportExcel_ServerClick(object sender, EventArgs e) { HttpCookie getCookies = Request.Cookies["UserLogin"]; if (getCookies != null) { var timeSearch = string.Empty; if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim())) { var dtBegin = DateTime.Now.ToString("yyyy/MM/dd"); var dtEnd = DateTime.Now.ToString("yyyy/MM/dd"); timeSearch = dtBegin + " - " + dtEnd; } else { timeSearch = txtTimeSelect.Value.Trim(); } var getBadProData = SearchDataClass.GetSearchUserRechargeInfoData(txtRechargeTel.Value.Trim(), drpRechargeStatus.SelectedValue, timeSearch); ArrayList ColTitle = new ArrayList() { "充值流水号", "充值日期", "充值手机", "充值人", "充值金额", "充值名称", "充值状态" }; //string[] strTitle = new string[] { "ASNNo", "SKU", "SKUDescrC", "ExpectedQty", "ReceivedQty", "UOM", "ReceivingLocation", "ReceivedTime", "CustomerID", "CodeName_C" }; if (getBadProData.ToList().Count > 0) { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //创建一个sheet Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //为单元格添加样式 Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线 style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线 style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线 style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线 //给各列的标题行PutValue赋值 int currow = 0; byte curcol = 0; sheet.Cells.ImportCustomObjects((System.Collections.ICollection)getBadProData, null, true, 0, 0, getBadProData.Count, true, "yyyy/MM/dd HH:mm:ss", false); // 设置内容样式 for (int i = 0; i < getBadProData.ToList().Count; i++) { sheet.Cells[i + 1, 0].PutValue(getBadProData[i].RecNo); sheet.Cells[i + 1, 1].PutValue(getBadProData[i].RecTime); sheet.Cells[i + 1, 2].PutValue(getBadProData[i].Tel); sheet.Cells[i + 1, 3].PutValue(getBadProData[i].UserName); sheet.Cells[i + 1, 4].PutValue(getBadProData[i].RecMoeny); var recstatus = string.Empty; var successinfo = string.Empty; switch (getBadProData[i].RecStatus) { case 'N': recstatus = "交易失败"; successinfo = "充值失败"; break; case 'Y': recstatus = "交易成功"; successinfo = "充值成功"; break; //case 'S': // recstatus = "分红成功"; // successinfo = "充值成功"; // break; case 'C': recstatus = "撤销充值"; successinfo = "撤销充值成功"; break; } //if (getBadProData[i].RecStatus == 'N') //{ // recstatus = "交易失败"; // successinfo = "充值失败"; //} //else //{ // recstatus = "交易成功"; // successinfo = "充值成功"; //} sheet.Cells[i + 1, 5].PutValue(getBadProData[i].RecContent == "" ? "东方柏农-" + getBadProData[i].RecTime.ToString("yyyy.MM.dd") + "-" + successinfo : getBadProData[i].RecContent); sheet.Cells[i + 1, 6].PutValue(recstatus); for (int j = 0; j < 7; j++) { sheet.Cells[i + 1, j].Style = style; sheet.Cells[i + 1, 1].Style.Custom = "yyyy/MM/dd HH:mm:ss"; //sheet.Cells[i + 1, 5].Style.Custom = "yyyy/MM/dd HH:mm:ss"; } } // 设置标题样式及背景色 foreach (string s in ColTitle) { sheet.Cells[currow, curcol].PutValue(s); style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); style.Pattern = Aspose.Cells.BackgroundType.Solid; style.Font.IsBold = true; sheet.Cells[currow, curcol].Style = style; curcol++; } Aspose.Cells.Cells cells = sheet.Cells; //设置标题行高 cells.SetRowHeight(0, 30); //让各列自适应宽度 sheet.AutoFitColumns(); //生成数据流 System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); //客户端保存的文件名 string fileName = "用户充值列表导出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //以字符流的形式下载文件 // Response.ContentType = "application/vnd.ms-excel"; //通知浏览器下载文件而不是打开 Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); Response.BinaryWrite(bt); Response.Flush(); Response.End(); } } }
public byte[] ExportSourceGoodsSC(string beg, string end, string changjia, string offerstatus, string flowstatus) { using (MySqlDbConnection dbc = MySqlConnstr.GetDBConnection()) { try { string where = ""; if (!string.IsNullOrEmpty(beg)) { where += " and a.addtime>='" + Convert.ToDateTime(beg).ToString("yyyy-MM-dd") + "'"; } if (!string.IsNullOrEmpty(end)) { where += " and a.addtime<='" + Convert.ToDateTime(end).AddDays(1).ToString("yyyy-MM-dd") + "'"; } if (!string.IsNullOrEmpty(changjia.Trim())) { where += " and " + dbc.C_Like("b.username", changjia.Trim(), SmartFramework4v2.Data.LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(offerstatus)) { where += " and " + dbc.C_EQ("a.offerstatus", offerstatus.Trim()); } if (!string.IsNullOrEmpty(flowstatus)) { where += " and " + dbc.C_EQ("a.flowstatus", flowstatus.Trim()); } string str = @"select a.*,b.username,b.carriername,c.name vehiclelengthrequirementname from tb_b_sourcegoodsinfo_offer a left join tb_b_user b on a.shipperid=b.userid left join tb_b_dictionary_detail c on a.vehiclelengthrequirement=c.bm where (a.shipperid in( select d.userid from tb_b_operator_association d left join tb_b_user e on d.userid=e.userid inner join tb_b_user f on d.operator=f.userid and f.correlationid=" + dbc.ToSqlValue(SystemUser.CurrentUser.UserID) + @" where d.status = 0 ) or 'D4D659F2-C2AE-4D96-AA87-A5DF0EC3F57C'=" + dbc.ToSqlValue(SystemUser.CurrentUser.UserID.ToUpper()) + @")"; str += where; //开始取分页数据 System.Data.DataTable dt = new System.Data.DataTable(); dt = dbc.ExecuteDataTable(str + " order by a.offerstatus asc, a.flowstatus asc, a.goodsinsertdatetime desc"); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 #region 样式 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 #endregion String[] ColumnName = { "厂家", "询价时间", "询价状态", "询价流程状态", "单号", "起始地", "目的地", "收货地址", "收货方", "收货联系人", "收货联系方式", "货物", "数量", "重量", "体积", "车型", "车长", "承运方", "是否提货", "是否送货", "预付运费", "预估企业报价", "预估下游成本", "预估税费成本", "预估资金成本", "备注", "预估用油金额", "预估用票金额" }; String[] ColumnV = { "username", "addtime", "offerstatus", "flowstatus", "shippingnotenumber", "goodsfromroute", "goodstoroute", "goodsreceiptplace", "consignee", "consicontactname", "consitelephonenumber", "descriptionofgoods", "totalnumberofpackages", "itemgrossweight", "cube", "vehicletyperequirement", "vehiclelengthrequirementname", "carriername", "istakegoods", "isdelivergoods", "actualcompanypay", "totalmonetaryamount", "estimatemoney", "estimatetaxmoney", "estimatecostmoney", "memo", "estimateoilmoney", "estimatevotemoney" }; for (int i = 0; i < ColumnName.Length; i++) { cells.SetColumnWidth(i, 30); cells[0, i].PutValue(ColumnName[i]); cells[0, i].SetStyle(style1); } if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { for (int a = 0; a < ColumnV.Length; a++) { var ColumnVs = dt.Rows[i][ColumnV[a]].ToString(); if (ColumnV[a] == "offerstatus") { switch (dt.Rows[i][ColumnV[a]].ToString()) { case "0": ColumnVs = "已咨询"; break; case "1": ColumnVs = "已下单"; break; } } if (ColumnV[a] == "flowstatus") { switch (dt.Rows[i][ColumnV[a]].ToString()) { case "0": str = "询价单等待市场部转发操作"; break; case "10": str = "询价单已发送操作员,等待报价"; break; case "20": str = "操作员已报价,等待市场部提交客户报价"; break; case "90": str = "市场部已提交客户报价,等待客户下单"; break; } } if (ColumnV[a] == "vehicletyperequirement") { switch (dt.Rows[i][ColumnV[a]].ToString()) { case "1": str = "栏板车"; break; case "2": str = "厢车"; break; } } if (ColumnV[a] == "istakegoods") { switch (dt.Rows[i][ColumnV[a]].ToString()) { case "0": str = "提货"; break; case "1": str = "不提货"; break; } } if (ColumnV[a] == "isdelivergoods") { switch (dt.Rows[i][ColumnV[a]].ToString()) { case "0": str = "送货"; break; case "1": str = "不送"; break; } } cells[i + 1, a].PutValue(ColumnVs); cells[i + 1, a].SetStyle(style2); } } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public byte[] GetThreeczgmmxOutLIST(string threezh, string zxname, string ddh, string beg, string end) { using (DBConnection dbc = new DBConnection()) { try { string where = ""; if (!string.IsNullOrEmpty(threezh.Trim())) { where += " and " + dbc.C_Like("c.UserName", threezh.Trim(), LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(zxname.Trim())) { where += " and " + dbc.C_Like("c.UserXM", zxname.Trim(), LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(ddh.Trim())) { where += " and " + dbc.C_EQ("a.OrderCode", ddh.Trim()); } if (!string.IsNullOrEmpty(beg)) { where += " and a.AddTime>='" + Convert.ToDateTime(beg).ToString("yyyy-MM-dd") + "'"; } if (!string.IsNullOrEmpty(end)) { where += " and a.AddTime<'" + Convert.ToDateTime(end).AddDays(1).ToString("yyyy-MM-dd") + "'"; } string str = @"select c.UserName,c.UserXM,a.AddTime,a.OrderCode,a.Money,a.redenvelopemoney,a.Points as ishb,d.points from tb_b_order a left join tb_b_plattosale b on a.PlatToSaleId=b.PlatToSaleId left join tb_b_user c on b.UserID=c.UserID left join tb_b_mydonate d on a.OrderCode=d.ordercode where b.pointkind=5 and a.status = 0 and zhifuzt = 1 "; str += where; //开始取分页数据 System.Data.DataTable dt = dbc.ExecuteDataTable(str + " order by a.AddTime desc"); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿 Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表 Aspose.Cells.Cells cells = sheet.Cells; //单元格 //样式1 Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "宋体"; //文字字体 style1.Font.Size = 12; //文字大小 style1.IsTextWrapped = true; //单元格内容自动换行 style1.Font.IsBold = true; //粗体 style1.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style1.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式2 Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Font.IsBold = false; //粗体 style2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居左 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 20; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Font.IsBold = true; //粗体 //样式4 Aspose.Cells.Style style4 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style4.HorizontalAlignment = TextAlignmentType.Left; //文字居左 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 10; //文字大小 style4.Font.Color = System.Drawing.Color.Red; //文字大小 style4.IsTextWrapped = true; //单元格内容自动换行 style4.Font.IsBold = false; //粗体 //第一行标题列表 //合并单元格cells.Merge(1, 0, 3, 1) 参数1代表当前行,参数0代表当前行当前列即第一行第一列,参数3合并的行数,参数4合并的列数 cells.Merge(0, 0, 1, 9); cells[0, 0].PutValue("三方充值购买明细"); cells[0, 0].SetStyle(style3); cells.SetRowHeight(0, 30); cells.SetColumnWidth(0, 30); cells[2, 0].PutValue("三方账号"); cells[2, 0].SetStyle(style1); cells.SetColumnWidth(1, 30); cells[2, 1].PutValue("专线名称"); cells[2, 1].SetStyle(style1); cells.SetColumnWidth(2, 30); cells[2, 2].PutValue("交易时间"); cells[2, 2].SetStyle(style1); cells.SetColumnWidth(3, 30); cells[2, 3].PutValue("订单编号"); cells[2, 3].SetStyle(style1); cells.SetColumnWidth(4, 30); cells[2, 4].PutValue("充值金额"); cells[2, 4].SetStyle(style1); cells.SetColumnWidth(5, 30); cells[2, 5].PutValue("实际支付金额"); cells[2, 5].SetStyle(style1); cells.SetColumnWidth(6, 30); cells[2, 6].PutValue("是否使用红包"); cells[2, 6].SetStyle(style1); cells.SetColumnWidth(7, 30); cells[2, 7].PutValue("红包金额"); cells[2, 7].SetStyle(style1); cells.SetColumnWidth(8, 30); cells[2, 8].PutValue("赠送券额"); cells[2, 8].SetStyle(style1); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 3, 0].PutValue(dt.Rows[i]["UserName"].ToString()); cells[i + 3, 0].SetStyle(style2); cells[i + 3, 1].PutValue(dt.Rows[i]["UserXM"].ToString()); cells[i + 3, 1].SetStyle(style2); cells[i + 3, 2].PutValue(Convert.ToDateTime(dt.Rows[i]["AddTime"].ToString()).ToString("yyyy-MM-dd")); cells[i + 3, 2].SetStyle(style2); cells[i + 3, 3].PutValue(dt.Rows[i]["OrderCode"].ToString()); cells[i + 3, 3].SetStyle(style2); cells[i + 3, 4].PutValue(dt.Rows[i]["ishb"].ToString()); cells[i + 3, 4].SetStyle(style2); cells[i + 3, 5].PutValue(dt.Rows[i]["Money"].ToString()); cells[i + 3, 5].SetStyle(style2); cells[i + 3, 6].PutValue(dt.Rows[i]["redenvelopemoney"].ToString()); cells[i + 3, 6].SetStyle(style2); cells[i + 3, 7].PutValue(dt.Rows[i]["points"].ToString()); cells[i + 3, 7].SetStyle(style2); } } System.IO.MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }