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