/// <summary>
/// OwcExecelHelper 的摘要说明
/// </summary>
public class OwcExcelHelper
{
public OwcExcelHelper(string fullFileName)
{
this._fullFileName = fullFileName;
}
private readonly string _fullFileName;
private SpreadsheetClass _excelNotional = new SpreadsheetClass();
/// <summary>
/// 物理路径名
/// </summary>
public string FullFileName
{
get { return this._fullFileName; }
}
/// <summary>
/// 将一个DataTable导出为Execel
/// </summary>
/// <param name="table">数据源</param>
/// <returns>从新生成的Excel物理文件上得到的内存流</returns>
public void ExportToExcel(DataTable table)
{
DataSet ds = new DataSet();
ds.Tables.Add(table);
this.ExportToExcel(ds);
}
/// <summary>
/// 将一个DataSet导出为Execel
/// </summary>
/// <param name="dataSet">数据源</param>
/// <returns>从新生成的Excel物理文件上得到的内存流</returns>
public void ExportToExcel(DataSet dataSet)
{
Workbook workbook = this._excelNotional.ActiveWorkbook;
CreateWorksheet(workbook, dataSet);
this._excelNotional.Export(
this._fullFileName,
SheetExportActionEnum.ssExportActionNone,
SheetExportFormat.ssExportAsAppropriate);
}
/// <summary>
/// 创建工作页
/// </summary>
private void CreateWorksheet(Workbook workbook, DataSet dataSet)
{
(workbook.Worksheets[workbook.Worksheets.Count] as Worksheet).Delete();
(workbook.Worksheets[workbook.Worksheets.Count] as Worksheet).Delete();
if (dataSet.Tables.Count > 0)
{
(workbook.Worksheets[1] as Worksheet).Name = dataSet.Tables[0].TableName;
}
for (int i = 1; i < dataSet.Tables.Count; i++)
{
Worksheet worksheet = (workbook.Worksheets.Add(Type.Missing, workbook.Worksheets[i], 1, Type.Missing)) as Worksheet;
worksheet.Name = dataSet.Tables[i].TableName;
}
for (int i = 0; i < dataSet.Tables.Count; i++)
{
CreateFirstRow((workbook.Worksheets[i + 1]) as Worksheet, dataSet.Tables[i]);
CreateOtherRows((workbook.Worksheets[i + 1]) as Worksheet, dataSet.Tables[i]);
}
}
/// <summary>
/// 设置给定工作页的首行
/// </summary>
private void CreateFirstRow(Worksheet worksheet, DataTable table)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Range range = (worksheet.Cells[1, i + 1] as Range);
range.Font.set_Bold(true);
range.set_RowHeight(30);
range.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
range.set_VerticalAlignment(XlVAlign.xlVAlignCenter);
range.Value2 = table.Columns[i].ToString();
}
}
/// <summary>
/// 设置给定工作页的首行之外的其他行
/// </summary>
private void CreateOtherRows(Worksheet worksheet, DataTable table)
{
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
(worksheet.Cells[i + 2, j + 1] as Range).Value2 = table.Rows[i][j];
}
}
}
/// <summary>
/// 获取内存流
/// </summary>
/// <returns></returns>
public MemoryStream GetMemoryStream()
{
const int BUFFER_SIZE = 4096;
MemoryStream memoryStream = new MemoryStream();
using (FileStream fileStream = new FileStream(this._fullFileName, FileMode.Open))
{
using (BufferedStream bufferedStream = new BufferedStream(fileStream))
{
byte[] buffer = new byte[BUFFER_SIZE];
int bufferLength;
while ((bufferLength = bufferedStream.Read(buffer, 0, BUFFER_SIZE)) > 0)
{
memoryStream.Write(buffer, 0, bufferLength);
}
}
}
return memoryStream;
}
}
==============================
<summary>
/// OwcChartHelper 的摘要说明
/// </summary>
public class OwcChartHelper
{
public OwcChartHelper(string fullFileName)
{
this._fullFileName = fullFileName;
}
private readonly string _fullFileName;
private const int DEFAULT_CHART_WIDTH = 500;
private const int DEFAULT_CHART_HEIGHT = 500;
private const string FILE_EXTEND = "gif";
private ChartSpace _chartSpace = new ChartSpace();
/// <summary>
/// 物理路径名，gif格式
/// </summary>
public string FullFileName
{
get { return this._fullFileName; }
}
#region 生成柱状图
/// <summary>
/// 生成柱状图
/// </summary>
/// <param name="table">数据源,第一列为x座标，第二列为Y轴上的数值，第三列之后为数值多序列</param>
/// <param name="is3D">是否显示为3D效果</param>
/// <param name="hasLegent">是否显示图例</param>
/// <param name="showPointY">是否显示交叉点的Y轴数据</param>
/// <param name="chartWidth">图表的宽度</param>
/// <param name="chartHeight">图表的高度</param>
/// <returns></returns>
public void CreateColumnChart(DataTable table, bool is3D, bool hasLegent, bool showPointY, int chartWidth, int chartHeight)
{
ChChart chart = this._chartSpace.Charts.Add(0); //在图表空间中添加一个图表
chart.Type = is3D ? ChartChartTypeEnum.chChartTypeColumnClustered3D : ChartChartTypeEnum.chChartTypeColumnClustered; //图表类型
chart.HasTitle = true; chart.Title.Caption = table.TableName; //设置图表标题
chart.HasLegend = hasLegent; //是否有图例
chart.Axes[0].HasTitle = true; chart.Axes[0].Title.Caption = table.Columns[0].ColumnName; //x轴的标题
if (table.Columns.Count == 2)
{
//当图表为一个序列时，才设置y轴的标题，多个序列时，y轴的标题无法确定
chart.Axes[1].HasTitle = true; chart.Axes[1].Title.Caption = table.Columns[1].ColumnName; //y轴的标题
}
//将类型字段保存到一个字符串变量，用"/t"分隔
string typeString = "";
foreach (DataRow row in table.Rows)
{
typeString += row[0].ToString() + "/t";
}
for (int i = 1; i < table.Columns.Count; i++)
{
//将值字段保存到一个字符串变量，用"/t"分隔
string dataString = "";
foreach (DataRow row in table.Rows)
{
dataString += row[i].ToString() + "/t";
}
//新建一个序列
ChSeries series = chart.SeriesCollection.Add(0);
series.Caption = table.Columns[i].ColumnName.ToString();
//设置类型字段(x轴)的值
series.SetData(ChartDimensionsEnum.chDimCategories, (int)ChartSpecialDataSourcesEnum.chDataLiteral, typeString);
//设置值字段(y轴)的值
series.SetData(ChartDimensionsEnum.chDimValues, (int)ChartSpecialDataSourcesEnum.chDataLiteral, dataString);
if (showPointY)
{
//值标签
series.DataLabelsCollection.Add().HasValue = true;
}
}
//生成图表，并保存为图片
this._chartSpace.ExportPicture(this._fullFileName, FILE_EXTEND, chartWidth, chartHeight);
}
/// <summary>
/// 生成柱状图
/// </summary>
/// <param name="table">数据源,第一列为x座标，第二列为Y轴上的数值，第三列之后为数值多序列</param>
/// <param name="chartWidth">图表的宽度</param>
/// <param name="chartHeight">图表的高度</param>
/// <returns></returns>
public void CreateColumnChart(DataTable table, int chartWidth, int chartHeight)
{
this.CreateColumnChart(table, false, true, true, chartWidth, chartHeight);
}
/// <summary>
/// 生成柱状图
/// </summary>
/// <param name="table">数据源,第一列为x座标，第二列为Y轴上的数值，第三列之后为数值多序列</param>
/// <returns></returns>
public void CreateColumnChart(DataTable table)
{
this.CreateColumnChart(table, false, true, true, DEFAULT_CHART_WIDTH, DEFAULT_CHART_HEIGHT);
}
#endregion
#region 生成饼状图
/// <summary>
/// 生成饼状图
/// </summary>
/// <param name="table">数据源，第一列为分类，第二列为数值，第三列及之后的列不处理</param>
/// <param name="is3D">是否显示为3D效果</param>
/// <param name="hasLegent">是否显示图例</param>
/// <param name="chartWidth">图表的宽度</param>
/// <param name="chartHeight">图表的高度</param>
/// <returns></returns>
public void CreatePieChart(DataTable table, bool is3D, bool hasLegent, int chartWidth, int chartHeight)
{
ChChart chart = this._chartSpace.Charts.Add(0);
chart.Type = is3D ? ChartChartTypeEnum.chChartTypePie3D : ChartChartTypeEnum.chChartTypePie;
chart.HasTitle = true;
chart.Title.Caption = table.TableName;
chart.HasLegend = hasLegent;
string typeString = "";
string dataString = "";
foreach (DataRow row in table.Rows)
{
typeString += row[0].ToString() + ":(" + row[1].ToString() + ")/t";
dataString += row[1].ToString() + "/t";
}
ChSeries series = chart.SeriesCollection.Add(0);
series.SetData(ChartDimensionsEnum.chDimCategories, (int)ChartSpecialDataSourcesEnum.chDataLiteral, typeString);
series.SetData(ChartDimensionsEnum.chDimValues, (int)ChartSpecialDataSourcesEnum.chDataLiteral, dataString);
//值标签
ChDataLabels labels = series.DataLabelsCollection.Add();
labels.HasPercentage = true;
labels.HasValue = false;
this._chartSpace.ExportPicture(this._fullFileName, FILE_EXTEND, chartWidth, chartHeight);
}
/// <summary>
/// 生成饼状图
/// </summary>
/// <param name="table">数据源，第一列为分类，第二列为数值，第三列及之后的列不处理</param>
/// <param name="chartWidth">图表的宽度</param>
/// <param name="chartHeight">图表的高度</param>
/// <returns></returns>
public void CreatePieChart(DataTable table, int chartWidth, int chartHeight)
{
this.CreatePieChart(table, false, true, chartWidth, chartHeight);
}
/// <summary>
/// 生成饼状图
/// </summary>
/// <param name="table">数据源，第一列为分类，第二列为数值，第三列及之后的列不处理</param>
/// <returns></returns>
public void CreatePieChart(DataTable table)
{
this.CreatePieChart(table, false, true, DEFAULT_CHART_WIDTH, DEFAULT_CHART_HEIGHT);
}
#endregion
#region 生成曲线图
/// <summary>
/// 生成曲线图
/// </summary>
/// <param name="table">数据源,第一列为x座标，第二列及之后的列为Y轴上的数值</param>
/// <param name="hasLegent">是否显示图例</param>
/// <param name="showPointY">是否显示交叉点的Y轴数据</param>
/// <param name="chartWidth">图表的宽度</param>
/// <param name="chartHeight">图表的高度</param>
/// <returns></returns>
public void CreateSmoothLineChart(DataTable table, bool hasLegent, bool showPointY, int chartWidth, int chartHeight)
{
ChChart chart = this._chartSpace.Charts.Add(0); //在图表空间中添加一个图表
chart.Type = ChartChartTypeEnum.chChartTypeSmoothLine; //设置图表类型
chart.HasTitle = true; chart.Title.Caption = table.TableName; //设置图表标题
chart.HasLegend = hasLegent; //是否有图例
chart.Axes[0].HasTitle = true; chart.Axes[0].Title.Caption = table.Columns[0].ColumnName; //x轴的标题
if (table.Columns.Count == 2)
{
//当图表为一个序列时，才设置y轴的标题，多个序列时，y轴的标题无法确定
chart.Axes[1].HasTitle = true; chart.Axes[1].Title.Caption = table.Columns[1].ColumnName; //y轴的标题
}
//将类型字段保存到一个字符串变量，用"/t"分隔
string typeString = "";
foreach (DataRow row in table.Rows)
{
typeString += row[0].ToString() + "/t";
}
for (int i = 1; i < table.Columns.Count; i++)
{
//将值字段保存到一个字符串变量，用"/t"分隔
string dataString = "";
foreach (DataRow row in table.Rows)
{
dataString += row[i].ToString() + "/t";
}
//新建一个序列
ChSeries series = chart.SeriesCollection.Add(0);
series.Caption = table.Columns[i].ColumnName.ToString();
//设置类型字段(x轴)的值
series.SetData(ChartDimensionsEnum.chDimCategories, (int)ChartSpecialDataSourcesEnum.chDataLiteral, typeString);
//设置值字段(y轴)的值
series.SetData(ChartDimensionsEnum.chDimValues, (int)ChartSpecialDataSourcesEnum.chDataLiteral, dataString);
//值标签
if (showPointY)
{
series.DataLabelsCollection.Add().HasValue = true;
}
}
//生成图表，并保存为图片
this._chartSpace.ExportPicture(this._fullFileName, FILE_EXTEND, chartWidth, chartHeight);
}
/// <summary>
/// 生成曲线图
/// </summary>
/// <param name="table">数据源,第一列为x座标，第二列及之后的列为Y轴上的数值</param>
/// <param name="chartWidth">图表的宽度</param>
/// <param name="chartHeight">图表的高度</param>
/// <returns></returns>
public void CreateSmoothLineChart(DataTable table, int chartWidth, int chartHeight)
{
this.CreateSmoothLineChart(table, true, false, chartWidth, chartHeight);
}
/// <summary>
/// 生成曲线图
/// </summary>
/// <param name="table">数据源,第一列为x座标，第二列及之后的列为Y轴上的数值</param>
/// <returns></returns>
public void CreateSmoothLineChart(DataTable table)
{
this.CreateSmoothLineChart(table, true, false, DEFAULT_CHART_WIDTH, DEFAULT_CHART_HEIGHT);
}
#endregion
public MemoryStream GetMemoryStream()
{
const int BUFFER_SIZE = 4096;
MemoryStream memoryStream = new MemoryStream();
using (FileStream fileStream = new FileStream(this._fullFileName, FileMode.Open))
{
using (BufferedStream bufferedStream = new BufferedStream(fileStream))
{
byte[] buffer = new byte[BUFFER_SIZE];
int bufferLength;
while ((bufferLength = bufferedStream.Read(buffer, 0, BUFFER_SIZE)) > 0)
{
memoryStream.Write(buffer, 0, bufferLength);
}
}
}
return memoryStream;
// MemoryStream memoryStream = new MemoryStream(System.IO.File.ReadAllBytes(this._fullFileName));
//FileStream f = new FileStream(this._fullFileName, FileMode.Open);
//MemoryStream memoryStream = new MemoryStream();
//memoryStream.SetLength(f.Length);
//f.Read(memoryStream.GetBuffer(), 0, (int)f.Length);
}
}
============================================
具体调用
string fileName = @"F:/temp/" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
OwcExcelHelper excel = new OwcExcelHelper(fileName);
excel.ExportToExcel(dataSet);
MemoryStream memoryStream = excel.GetMemoryStream();
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
Response.AddHeader("Content-Length", memoryStream.Length.ToString());
Response.ContentType = "application/vnd.ms-excel";
//ms.WriteTo(Response.OutputStream);
byte[] bytes = memoryStream.GetBuffer();
Response.OutputStream.Write(bytes, 0, bytes.Length);
Response.Flush();
HttpContext.Current.Response.End();
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
private void CreateCharts()
{
DataTable table = this.GetDate();
table.TableName = "渠道分析";
table.Columns[0].ColumnName = "代理商";
table.Columns[1].ColumnName = "销售金额";
table.Columns[2].ColumnName = "成本金额";
生成柱状图
//string fileName1 = @"F:/temp/column_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".gif";
//OwcChartHelper chart1 = new OwcChartHelper(fileName1);
//chart1.CreateColumnChart(table, false, true, true, 1024, 800); //柱状图
//MemoryStream memoryStream1 = chart1.GetMemoryStream();
//Response.ContentType = "image/gif";
//Response.BinaryWrite(memoryStream1.ToArray());
饼图
//string fileName2 = @"F:/temp/pie_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".gif";
//OwcChartHelper chart2 = new OwcChartHelper(fileName2);
//chart2.CreatePieChart(table); //柱状图
//MemoryStream memoryStream2 = chart2.GetMemoryStream();
//Response.ContentType = "image/gif";
//Response.BinaryWrite(memoryStream2.ToArray());
//曲线图
string fileName3 = @"F:/temp/ling_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".gif";
OwcChartHelper chart3 = new OwcChartHelper(fileName3);
chart3.CreateSmoothLineChart(table);
MemoryStream memoryStream3 = chart3.GetMemoryStream(); //柱状图
Response.ContentType = "image/gif";
Response.BinaryWrite(memoryStream3.ToArray());
}
543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
