仅仅表格导出excel毕竟容易，但是加上图就麻烦了，折磨我近一个星期，
涉及到各种样式，网上还搜不到帮助文档，结果我一边谷歌翻译，一边敲代码。
先看不涉及到图表的代码
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (excelApp == null)//检测服务器电脑中有没有安装excel
{
HttpContext.Current.Response.Write("excel error");
throw new Exception("服务器无excel");
}
else
{
string filename = this.GetExcelName();
Microsoft.Office.Interop.Excel.Workbook workBook = excelApp.Workbooks.Add(true);
if (File.Exists(HttpContext.Current.Server.MapPath(filename)))
{
File.Delete(HttpContext.Current.Server.MapPath(filename));
}
Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(1);//获得第1个sheet，准备写入
//合并单元格
Microsoft.Office.Interop.Excel.Range excelRange;
this.MergeCell(out excelRange, workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, 19]));
//设置样式
Microsoft.Office.Interop.Excel.Range Range = workSheet.get_Range("A1", "W100");
this.ExcelStyle(ref Range, XlHAlign.xlHAlignCenter, XlHAlign.xlHAlignCenter, 20, 20);
//赋值方式
workSheet.Cells[5, 1] = "综合计划处：";
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="Range">范围实体</param>
/// <param name="Cells">范围（例：workSheet.get_Range(workSheet.Cells[3, 1], workSheet.Cells[4, 7])）</param>
private void MergeCell(out Range Range, Range Cells)
{
try
{
Range = Cells;
Range.Application.DisplayAlerts = false;
Range.Merge(Missing.Value);
Range.Application.DisplayAlerts = true;
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 设置excel行、列样式
/// </summary>
/// <param name="Range">范围（例如：1：(Excel.Range)excelWorkSheet.Rows[CellRowID, Missing.Value]） <br/> 2:worksheet.get_Range("A1","A1")</param>
/// <param name="HorizontalAlignment">文本水平居中方式 （如:XlHAlign.xlHAlignCenter）</param>
/// <param name="VerticalAlignment">文本垂直居中方式 </param>
/// <param name="RowHieght">行高(单位MM)</param>
/// <param name="ColumnWidth">列宽度（单位MM） </param>
/// <param name="FontName">字体名称（如：宋体）</param>
/// <param name="FontStyle">字体样式（如：加粗）</param>
/// <param name="FontSize">字体大小（如:5）</param>
/// <param name="FontColor">字体颜色 </param>
/// <param name="Underline">字体是否有下划线（如:True）</param>
/// <param name="BordersLineStyle">设置单元格边框的粗细 （如:5）</param>
/// <param name="BorderAround">给单元格加边框（如:XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()） </param>
/// <param name="BordersGet_Item">设置单元格上边框为无边框 （如:(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone）</param>
/// <param name="CellsInteriorColor">设置单元格的背景色（如:System.Drawing.Color.FromArgb(255, 204, 153).ToArgb()） </param>
/// <param name="EntireColumnAutoFit">是否自动调整列宽 </param>
/// <param name="WrapText">是否文本自动换行 </param>
/// <param name="InteriorColorIndex">填充颜色为淡紫色 (可参考ColorIndex())</param>
private void ExcelStyle(ref Range Range, object VerticalAlignment = null, object HorizontalAlignment = null, object RowHieght = null, object ColumnWidth = null, object FontName = null, object FontStyle = null, object FontSize = null, object FontColor = null, object Underline = null, object BordersLineStyle = null, object BorderAround = null, XlBordersIndex BordersGet_Item = 0, object CellsInteriorColor = null, Boolean EntireColumnAutoFit = false, object WrapText = null, object InteriorColorIndex = null)
{
try
{
if (RowHieght != null)
{
Range.RowHeight = RowHieght;
}
if (ColumnWidth != null) //设置单元格的宽度
{
Range.ColumnWidth = ColumnWidth;
}
if (FontName != null)
{
Range.Font.Name = FontName;
}
if (FontStyle != null)
{
Range.Font.FontStyle = FontStyle;
}
if (FontSize != null)
{
Range.Font.Size = FontSize;
}
if (Underline != null)
{
Range.Font.Underline = true;
}
if (BordersLineStyle != null)
{
Range.Borders.LineStyle = BordersLineStyle;
}
if (BorderAround != null)
{
Range.BorderAround(BorderAround);
}
if (BordersGet_Item != 0)
{
Range.Borders.get_Item(BordersGet_Item);
}
if (HorizontalAlignment != null)
{
Range.HorizontalAlignment = HorizontalAlignment;
}
if (CellsInteriorColor != null)
{
Range.Cells.Interior.Color = CellsInteriorColor;
}
if (EntireColumnAutoFit)
{
Range.EntireColumn.AutoFit();
}
if (VerticalAlignment != null)
{
Range.VerticalAlignment = VerticalAlignment;
}
if (WrapText != null)
{
Range.WrapText = WrapText;
}
if (InteriorColorIndex != null)
{
Range.Interior.ColorIndex = InteriorColorIndex;
}
if (FontColor != null)
{
Range.Font.Color = FontColor;
}
//以下是部分操作方式
//Range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//设置字体在单元格内的对其方式
//Range.ColumnWidth = 15;
//Range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //设置单元格的背景色
//Range.Borders.LineStyle = 1; //设置单元格边框的粗细
//Range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); //给单元格加边框
//Range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格上边框为无边框
//Range.EntireColumn.AutoFit(); //自动调整列宽
//Range.HorizontalAlignment = "xlCenter"; // 文本水平居中方式
//Range.VerticalAlignment = "xlCenter"; //文本垂直居中方式
//Range.WrapText=true; //文本自动换行
//Range.Interior.ColorIndex=39; //填充颜色为淡紫色
//Range.Font.Color="clBlue"; //字体颜色
}
catch (Exception ex)
{
throw ex;
}
}接下来看图表部分
CreateChart(workBook, workSheet, 7, 1, 1400, 600, "XXXXX表", workSheet.get_Range("T4:U19,A4:A19"), workSheet.get_Range("A4:A19"), workSheet.get_Range("T4:U19"), Convert.ToDouble(MinimumScale), Convert.ToDouble(MaximumScale + 10), "Chart 1");
/// <summary>
/// 创建图表
/// </summary>
/// <param name="m_Book">_Workbook</param>
/// <param name="m_Sheet">_Worksheet</param>
/// <param name="CharTop">距页面顶部位置（按格数算）</param>
/// <param name="CharLeft">距页面左侧位置（按格数算）</param>
/// <param name="Width">图表外框宽度</param>
/// <param name="Height">图表外框高度</param>
/// <param name="Title">图表标题名称</param>
/// <param name="range">要插入图表的范围值</param>
/// <param name="CategoryLabels">类别标签值</param>
/// <param name="SeriesLabels">系列标签值</param>
/// <param name="MinimumScale">x轴最小值</param>
/// <param name="MaximumScale">x轴最大值</param>
/// <param name="CharName">图表名称(为了区份操作的不是一个图，无其他用处)</param>
private void CreateChart(Microsoft.Office.Interop.Excel._Workbook m_Book, Microsoft.Office.Interop.Excel._Worksheet m_Sheet, int CharTop, int CharLeft, float Width, float Height, string Title, Range range, object CategoryLabels, object SeriesLabels, double MinimumScale, double MaximumScale,string CharName)
{
Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel.Series oSeries;
m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);
m_Book.ActiveChart.ChartWizard(range, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered, Type.Missing, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns, CategoryLabels, SeriesLabels, true, Title, "各市", "百分比(%)", Type.Missing);
//以下是给图表放在指定位置
m_Book.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name);
oResizeRange = (Microsoft.Office.Interop.Excel.Range)m_Sheet.Rows.get_Item(CharTop, Missing.Value);
m_Sheet.Shapes.Item(CharName).Top = (float)(double)oResizeRange.Top; //调图表的位置上边距
oResizeRange = (Microsoft.Office.Interop.Excel.Range)m_Sheet.Columns.get_Item(CharLeft, Missing.Value);
m_Sheet.Shapes.Item(CharName).Left = (float)(double)oResizeRange.Left;//调图表的位置左边距
m_Sheet.Shapes.Item(CharName).Width = Width; //调图表的宽度
m_Sheet.Shapes.Item(CharName).Height = Height; //调图表的高度
m_Book.ActiveChart._ApplyDataLabels();//数据标签
m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19; //设置绘图区的背景色
m_Book.ActiveChart.PlotArea.Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置绘图区边框线条
m_Book.ActiveChart.ChartArea.Border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置边框线条
m_Book.ActiveChart.HasDataTable = false;
//设置Legend图例的位置和格式
m_Book.ActiveChart.Legend.Interior.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexNone;
m_Book.ActiveChart.Legend.Font.Name = "宋体";
//设置X轴的显示
Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)m_Book.ActiveChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
xAxis.MajorGridlines.Border.ColorIndex = 1;//gridLine横向线条的颜色
xAxis.HasTitle = true;
xAxis.MinimumScale = MinimumScale;
xAxis.MaximumScale = MaximumScale;
xAxis.TickLabels.Font.Name = "宋体";
xAxis.TickLabels.Font.Size = 8;
//设置Y轴的显示
Microsoft.Office.Interop.Excel.Axis yAxis = (Microsoft.Office.Interop.Excel.Axis)m_Book.ActiveChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
yAxis.TickLabels.Orientation = Microsoft.Office.Interop.Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;//Y轴显示的方向,是水平还是垂直等
yAxis.TickLabels.Font.Size = 8;
yAxis.TickLabels.Font.Name = "宋体";
oSeries = (Microsoft.Office.Interop.Excel.Series)m_Book.ActiveChart.SeriesCollection(1);
oSeries.Border.ColorIndex = 45;
}结尾
//visable属性设置为true的话，excel程序会启动；false的话，excel只在后台运行。
excelApp.Visible = false;
//displayalert设置为true将会显示excel中的提示信息。
excelApp.DisplayAlerts = false;
workBook.SaveAs(HttpContext.Current.Server.MapPath(filename));
workBook.Close(false, Missing.Value, Missing.Value);
#region 清理机制
excelApp.Quit();
workSheet = null;
workBook = null;
excelApp = null;
GC.Collect();
#endregion
HttpContext.Current.Response.Write(filename);细节
1：Rang范围问题
workSheet.get_Range("T4:U19,A4:A19");获取范围的时候，其实可以获取多个范围。这句话的意思等同于
workSheet.get_Range("T4","U19");workSheet.get_Range("A4","A19");获取多个范围的时候可用“,”隔开，这样就能获取多个范围
2：关于设置图表，我发现有好多方式都能写出来，设计图表时，请多按F12，追根求源，不要被我写的方法干扰到，这样才能写出好的方法。
最后附送excel颜色枚举类
这是我在网上找到的
/// <summary>
/// 常用颜色定义,对就Excel中颜色名
/// </summary>
public enum ColorIndex
{
无色 = -4142, 自动 = -4105, 黑色 = 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49,
深蓝 = 11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 = 10,
青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 = 3, 浅橙色 = 45, 酸橙色 = 43,
海绿 = 50, 水绿色 = 42, 浅蓝 = 41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7,
金色 = 44, 黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 = 15,
玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39,
白色 = 2
}
200
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
