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