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);
}
public static MemoryStream ExportExcel(DataTable dt_excel, string sMapPath)
{
try
{
DirectoryInfo info = new DirectoryInfo(sMapPath);
if (!info.Exists)
{
info.Create();
}
Workbook doc = new Workbook();
doc.Shared = true;
Worksheet oSheet = doc.Worksheets[0];
Style style = doc.Styles[doc.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 8;
style.IsTextWrapped = true;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
oSheet.Cells.ApplyStyle(style, styleFlag);
for (int i = dt_excel.Columns.Count - 1; i >= 0; i--)
{
if (dt_excel.Columns[i].ColumnName.Contains("Hidden"))
{
dt_excel.Columns.RemoveAt(i);
}
}
for (int i = 0; i < dt_excel.Columns.Count; i++)
{
oSheet.Cells[0, i].PutValue(dt_excel.Columns[i].ColumnName);
oSheet.Cells[0, i].Style.Font.IsBold = true;
}
Style style1 = doc.Styles[doc.Styles.Add()]; //新增样式
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "Arial"; //文字字体
style1.Font.Size = 8; //文字大小
style1.IsLocked = false; //单元格解锁
//style1.Font.IsBold = true;//粗体
style1.ForegroundColor = System.Drawing.Color.FromArgb(0x99, 0xcc, 0xff); //设置背景色
style1.Pattern = BackgroundType.Solid; //设置背景样式
style1.IsTextWrapped = true; //单元格内容自动换行
for (int i = 0; i < dt_excel.Rows.Count; i++)
{
for (int j = 0; j < dt_excel.Columns.Count; j++)
{
if (dt_excel.Columns[j].ColumnName == "BookingDate" || dt_excel.Columns[j].ColumnName == "StatusDate")
{
oSheet.Cells[i + 1, j].PutValue(Convert.ToDateTime(dt_excel.Rows[i][j]).ToString("dd-MM-yyyy HH:mm:ss"));
}
else if (dt_excel.Columns[j].ColumnName == "WarningAlert" || dt_excel.Columns[j].ColumnName == "ConfirmationComment")
{
oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
oSheet.Cells[i + 1, j].SetStyle(style1);
}
//else if (dt_excel.Columns[j].ColumnName == "BookingNumber")
//{
// oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
// oSheet.Cells[i + 1, j].SetStyle(style1);
//}
else
{
oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
}
}
}
for (int i = 0; i < oSheet.Cells.MaxDataColumn; i++)
{
if (i != 8 && i != 9)
{
oSheet.AutoFitColumn(i);
}
else
{
oSheet.Cells.SetColumnWidth(i, 50);
}
}
//oSheet.column.SetColumnWidth(9, 40);
//oSheet.Cells.SetColumnWidth(10, 40);
return(doc.SaveToStream());
}
catch (Exception ex)
{
throw ex;
}
}
/// <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;
}
}
public static string ExportExcel(DataTable dt_excel, string sMapPath, string sFileName, Func <object, string> funConvertDateTime = null)
{
try
{
DirectoryInfo info = new DirectoryInfo(sMapPath);
if (!info.Exists)
{
info.Create();
}
string sFullPath = sMapPath + sFileName;
Workbook doc = new Workbook();
doc.Shared = true;
Worksheet oSheet = doc.Worksheets[0];
oSheet.Cells.SetColumnWidth(0, 30);
Style style = doc.Styles[doc.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 8;
style.IsTextWrapped = true;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
oSheet.Cells.ApplyStyle(style, styleFlag);
for (int i = dt_excel.Columns.Count - 1; i >= 0; i--)
{
if (dt_excel.Columns[i].ColumnName.Contains("Hidden"))
{
dt_excel.Columns.RemoveAt(i);
}
}
for (int i = 0; i < dt_excel.Columns.Count; i++)
{
oSheet.Cells[0, i].PutValue(dt_excel.Columns[i].ColumnName);
oSheet.Cells[0, i].Style.Font.IsBold = true;
}
for (int i = 0; i < dt_excel.Rows.Count; i++)
{
for (int j = 0; j < dt_excel.Columns.Count; j++)
{
oSheet.Cells.SetColumnWidth(j, 40);
if (dt_excel.Columns[j].DataType == typeof(DateTime) && funConvertDateTime != null)
{
oSheet.Cells[i + 1, j].PutValue(funConvertDateTime(dt_excel.Rows[i][j]));
}
else
{
oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]);
}
}
}
oSheet.AutoFitColumns();
doc.Save(sFullPath);
return(sFullPath);
}
catch (Exception ex)
{
throw ex;
}
}
private void btn导出_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count < 1)
{
return;
}
Workbook work = new Aspose.Cells.Workbook();
//Worksheet sheet = work.Worksheets["New Worksheet1"];
Worksheet sheet = default(Aspose.Cells.Worksheet);
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Font.Name = "宋体";
Aspose.Cells.Style stylecolor = new Aspose.Cells.Style();
stylecolor.ForegroundColor = Color.LightBlue;
Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
flag.Font = true;
//
sheet = work.Worksheets[0];
intExcelTempIndex += 1;
sheet.Name = "使用记录" + intExcelTempIndex.ToString();
int Rowi = 0;
int Rown = 0;
int RownMax = 0;
//最后Export列不导出
RownMax = System.Convert.ToInt32(dataGridView1.Columns.Count - 0);
ArrayList colList = new ArrayList(); //存放不显示的列
int Col = 0;
for (int i = 0; i < RownMax; i++)
{
if (dataGridView1.Columns[i].Width <= 5 || dataGridView1.Columns[i].Visible == false)
{
}
else
{
colList.Add(i);
sheet.Cells[0, Col].PutValue(dataGridView1.Columns[i].HeaderText);
Col++;
}
}
//表内容
int intcount = 0;
for (Rowi = 0; Rowi <= dataGridView1.Rows.Count - 1; Rowi++)
{
if (dataGridView1.Rows[Rowi].Visible == false)
{
intcount++;
continue;
}
for (Rown = 0; Rown <= colList.Count - 1; Rown++)
{
if (dataGridView1.Rows[Rowi].Cells[Rown].Value != null)
{
sheet.Cells[Rowi + 1 - intcount, Rown].PutValue(dataGridView1.Rows[Rowi].Cells[Rown].Value.ToString());
}
else
{
sheet.Cells[Rowi + 1 - intcount, Rown].PutValue("");
}
}
}
//reportToexcel();
string filepath = "";
sheet.Cells.ApplyStyle(style, flag);
if (dataGridView1.Rows.Count > 60000)
{
filepath = System.Windows.Forms.Application.StartupPath + "\\" + sheet.Name + ".xlsx";
}
else
{
filepath = System.Windows.Forms.Application.StartupPath + "\\" + sheet.Name + ".xls";
}
work.Save(filepath);
System.Diagnostics.Process.Start(filepath); //'打开导出Excel
}