/// <summary>
/// 生成Excel文件流
/// </summary>
/// <param name="titles"></param>
/// <param name="fields"></param>
/// <param name="list"></param>
/// <param name="saveFormat"></param>
/// <param name="isPropertyNameShown"></param>
/// <param name="firstRow"></param>
/// <param name="firstColumn"></param>
/// <param name="insertRows"></param>
/// <param name="dateFormatString"></param>
/// <param name="convertStringToNumber"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static byte[] GenerateExcelFileStream(string[] titles, string[] fields, ICollection list
, SaveFormat saveFormat, bool isPropertyNameShown, int firstRow, int firstColumn
, bool insertRows, string dateFormatString, bool convertStringToNumber, string sheetName = "Sheet1")
{
AsposeXls.Workbook workbook = new AsposeXls.Workbook();
AsposeXls.Worksheet sheet = workbook.Worksheets[0];
sheet.Name = sheetName;
sheet.FreezePanes(1, 1, 1, 0); //冻结第一行
for (int i = 0; i < titles.Length; i++)
{
sheet.Cells[0, i].PutValue(titles[i]);
}
sheet.Cells.ImportCustomObjects(list, fields, isPropertyNameShown, firstRow, firstColumn,
list.Count, insertRows, dateFormatString, convertStringToNumber);
sheet.AutoFitColumns();//让各列自适应宽度，这个很有用
using (MemoryStream stream = new MemoryStream())
{
workbook.Save(stream, saveFormat);
return(stream.ToArray());
}
}
public static void AutoFitExcel(string sourceExcelPath, string destinateExcelPath)
{
FileStream fstream = new FileStream(sourceExcelPath, FileMode.Open);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fstream);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
workbook.Save(destinateExcelPath);
fstream.Close();
}
/// <summary>
/// 生成Excel文件流
/// </summary>
/// <param name="result"></param>
/// <param name="isFieldNameShown"></param>
/// <param name="firstRow"></param>
/// <param name="firstColumn"></param>
/// <param name="saveFormat"></param>
/// <param name="rowNumber"></param>
/// <param name="colNumber"></param>
/// <param name="insertRows"></param>
/// <param name="dateFormatString"></param>
/// <param name="convertStringToNumber"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static byte[] GenerateExcelFileStream(DataTable result, bool isFieldNameShown
, int firstRow, int firstColumn, SaveFormat saveFormat, int rowNumber, int colNumber, bool insertRows, string dateFormatString
, bool convertStringToNumber, string sheetName = "Sheet1")
{
AsposeXls.Workbook workbook = new AsposeXls.Workbook();
AsposeXls.Worksheet sheet = workbook.Worksheets[0];
sheet.Name = sheetName;
sheet.FreezePanes(1, 1, 1, 0); // 冻结第一行
//sheet.Cells.ImportDataTable(result, isFieldNameShown, firstRow, firstColumn
// , rowNumber, colNumber, insertRows, dateFormatString, true);
sheet.Cells.ImportDataTable(result, true, 0, 0);
sheet.AutoFitColumns(); // 让各列自适应宽度，这个很有用
using (MemoryStream stream = new MemoryStream())
{
workbook.Save(stream, saveFormat);
return(stream.ToArray());
}
}
public bool ExportReportToExcel(System.Data.DataTable dt, string fileName, Dictionary <string, object> filter)
{
bool succeed = false;
if (dt != null)
{
try
{
//Aspose.Cells.License li = new Aspose.Cells.License();
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = dt.TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
if (colCount >= filter.Count)
{
//列名的处理
foreach (KeyValuePair <string, object> de0 in filter)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(de0.Key.ToString());//dt.Columns[i].ColumnName
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Font.IsBold = true;
style.Font.Name = "宋体";
cellSheet.Cells[rowIndex, colIndex].SetStyle(style);
colIndex++;
}
Aspose.Cells.Style style2 = new Aspose.Cells.Style();
style2.Font.Name = "Arial";
style2.Font.Size = 10;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
cellSheet.Cells.ApplyStyle(style2, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
foreach (KeyValuePair <string, object> de1 in filter)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][de1.Value.ToString()].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
//path = System.IO.Path.GetFullPath(fileName);
//workbook.Save(path);
cellSheet.AutoFitColumns();//让各列自适应宽度，这个很有用。
//Response.Clear();
//Response.Charset = "UTF8";
//Response.ContentEncoding = System.Text.Encoding.UTF8;
//Response.HeaderEncoding = System.Text.Encoding.UTF8;
//Response.ContentType = "application/ms-excel";
HttpResponse response = Page.Response;
response.Clear();
response.ContentType = "application/octet-stream";
//使用UTF-8对文件名进行编码
fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
response.ContentType = "application/ms-excel;";
//workbook.Save(fileName, Aspose.Cells.FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, response);
workbook.Save(fileName, FileFormatType.Xlsx);
succeed = true;
}
}
catch (Exception ex)
{
succeed = false;
}
}
return(succeed);
}
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();
}
}
}
/// <summary>
/// 检索数据
/// </summary>
/// <param name="baseFilter"></param>
/// <param name="responseEntity"></param>
public void ExportData(BaseFilter baseFilter, ResponseEntity responseEntity)
{
var filter = baseFilter as RelativeDayExportDunFilter;
if (filter == null)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.RequestCommandError);
return;
}
DataTable dt
= Singleton <RelativeDayDunTaskExportDAL <RelativeDaySearchDunExportViewData> > .Instance.SearchDataToDataTable(filter);
if (dt == null || dt.Rows.Count <= 0)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult, "无催收单导出");
m_Logger.Info("无催收单导出。");
return;
}
AsposeXls.Workbook workbook = new AsposeXls.Workbook();
AsposeXls.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Style sc1 = workbook.Styles[workbook.Styles.Add()];
sc1.ShrinkToFit = true;
sc1.Number = 14;
sc1.HorizontalAlignment = AsposeXls.TextAlignmentType.Center;
sc1.VerticalAlignment = AsposeXls.TextAlignmentType.Center;
Aspose.Cells.StyleFlag scf1 = new Aspose.Cells.StyleFlag();
scf1.ShrinkToFit = true;
scf1.NumberFormat = true;
scf1.HorizontalAlignment = true;
scf1.VerticalAlignment = true;
Aspose.Cells.Column colomn1 = sheet.Cells.Columns[9];
colomn1.ApplyStyle(sc1, scf1);
sheet.FreezePanes(1, 1, 1, 0); //冻结第一行
DataTable dtb = dt.Clone();
foreach (DataRow item in dt.Rows)
{
DataRow NewRow = dtb.NewRow();
for (int i = 0; i < NewRow.ItemArray.Length; i++)
{
NewRow[i] = item[i];
}
NewRow["客户类型"] = Convert.ToByte(item["客户类型"]).ValueToDesc <EnumBusinessStatus>();
NewRow["逾期月数"] = "M" + item["逾期月数"].ToString();
NewRow["客户状态"] = Convert.ToByte(item["客户状态"]).ValueToDesc <EnumLawsuitStatus>();
//NewRow["产品类型"] = Convert.ToByte(item["产品类型"]).ValueToDesc<EnumLawsuitStatus>();
NewRow["产品种类"] = Convert.ToByte(item["产品种类"]).ValueToDesc <EnumProductKind>();
dtb.Rows.Add(NewRow);
}
sheet.Cells.ImportDataTable(dtb, true, 0, 0);
sheet.AutoFitColumns();
byte[] result = null;
using (MemoryStream stream = new MemoryStream())
{
workbook.Save(stream, SaveFormat.Xlsx);
result = stream.ToArray();
}
if (result == null || result.Length == 0)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult);
m_Logger.Info("无催收单导出。");
}
else
{
// 设置输出文件
ResponseFileResult responseResult = new ResponseFileResult();
responseResult.Result = result;
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.Success);
responseEntity.Results = responseResult;
}
}
