创建Excel，加柱状图

雨点打透心脏的1/2处 2022-05-24 02:28 166阅读 0赞

仅仅表格导出excel毕竟容易，但是加上图就麻烦了，折磨我近一个星期，![发火][angry.gif]涉及到各种样式，网上还搜不到帮助文档，结果我一边谷歌翻译，一边敲代码。

## 先看不涉及到图表的代码    ##

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
            }

## 如果大家有什么建议和问题，欢迎下方评论，大家一起学习进步    ##

[微软excel开发文档][excel]

[angry.gif]: https://static-blog.csdn.net/xheditor/xheditor_emot/default/angry.gif
[excel]: https://msdn.microsoft.com/vba/vba-excel