/// <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());
}
}
/// <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());
}
}
/// <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;
}
}
