asp.net 数据导出到excel方法

 

using Aspose.Cells;

/// <summary>
/// 导出到excel
/// </summary>
/// <param name="path">excle模版</param>
/// <param name="dataTable">数据源dt</param>
/// <param name="filename">文件名</param>
/// <param name="_page"></param>
/// <param name="rowStart">开始写入的数据行（从0开始）</param>
/// <param name="colStart">开始写入的数据列</param>
/// <param name="stream">文件流</param>
/// <param name="freezeRow">冻结行</param>
/// <param name="freezeCol">冻结列</param>
/// <param name="tagsCol">文本格式列</param>
/// <param name="tagsRow">文本格式行</param>
public void Export(string path, DataTable dataTable, string filename, Page _page, int rowStart, int colStart, Stream[] stream, int freezeRow, int freezeCol, int[] tagsCol, int[] tagsRow)
{
path = HttpContext.Current.Server.MapPath(path);
Workbook workbook = new Workbook(path);
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets[1];
int t = 0;
foreach (DataRow dr in dataTable.Rows)
{
for (int c = 0; c < dataTable.Columns.Count; c++)
{
if (dr[c] != null && dr[c] != DBNull.Value && !string.IsNullOrEmpty(dr[c].ToString()))
{
string s = dr[c].ToString();
if (tagsRow.Contains(t))
{
sheet.Cells[rowStart, c + colStart].PutValue(s);
}
else
{
if (tagsCol.Contains(c))
{
sheet.Cells[rowStart, c + colStart].PutValue(s);
}
else
{
double v = 0;
if (double.TryParse(s, out v))
{
if (v != 0)
{
sheet.Cells[rowStart, c + colStart].PutValue(v);
}
}
}
}

}
}
t++;
rowStart++;
}

sheet.DisplayZeros = false;
sheet.FreezePanes(freezeRow, freezeCol, freezeRow, freezeCol);
sheet.AutoFitColumns();
sheet.AutoFitRows();
workbook.Settings.IsHScrollBarVisible = true;
workbook.Settings.IsVScrollBarVisible = true;
workbook.Settings.ConvertNumericData = true;
int i = 1;

//下面是保存mschart的图表
foreach (Stream s in stream)
{
int pictureIndex = sheet1.Pictures.Add(i, 1, s);
Aspose.Cells.Drawing.Picture picture = sheet1.Pictures[pictureIndex];
picture.BorderLineColor = System.Drawing.Color.FromArgb(69, 79, 129, 189);
picture.BorderWeight = 0.02;
i += 12;
}

string sheetName = Server.UrlEncode(filename);
sheet.Name = "数据";
sheet1.Name = "图表";
workbook.Save(HttpContext.Current.Response, sheetName, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
HttpContext.Current.Response.End();

}

 

方法2（直接赋值datable）

public void Export(string path, DataTable dataTable, string filename, Page _page, int row, int col, Stream[] stream, int freezeRow, int freezeCol)
{
path = HttpContext.Current.Server.MapPath(path);
Workbook workbook = new Workbook(path);
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets[1];
sheet.Cells.ImportDataTable(dataTable, false, row, col, false);
sheet.DisplayZeros = false;
sheet.FreezePanes(freezeRow, freezeCol, freezeRow, freezeCol);
sheet.AutoFitColumns();
sheet.AutoFitRows();
workbook.Settings.IsHScrollBarVisible = true;
workbook.Settings.IsVScrollBarVisible = true;
workbook.Settings.ConvertNumericData = true;
int i = 1;

//mschart 图表
foreach (Stream s in stream)
{
int pictureIndex = sheet1.Pictures.Add(i, 1, s, 120, 60);
Aspose.Cells.Drawing.Picture picture = sheet1.Pictures[pictureIndex];
picture.BorderLineColor = System.Drawing.Color.FromArgb(69, 79, 129, 189);
picture.BorderWeight = 0.02;
i += 12;
}

string sheetName = Server.UrlEncode(filename);
sheet.Name = "数据";
sheet1.Name = "图表";
workbook.Save(HttpContext.Current.Response, sheetName, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
HttpContext.Current.Response.End();

}

转载于:https://www.cnblogs.com/dxj143/archive/2012/09/11/2680186.html