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;
}
}
}