郁闷！C#导出EXCEL生成报表和柱状图遇到难题了！附大量代码大家来帮忙、学习参考下！

    前一阵，帮老板开发个数据分析系统，要能根据老板的想法利用选择的数据表的数据字段（包括数据字段之间互相运算生成新字段）来生成各种报表，现在系统完成了，但是还是很不完美。
    主要的问题是EXCEL生成图的问题，因为系统的数据表很多个表（数据量也很大，经常上几十万行数据），而且分析字段也不固定，这意味着不好利用实现准备好的模板，c#打开模板把数据写到相应的地方，自动就生成图的方法，因为图的DATATABLE区域是不固定的，行数和列数都不固定。
    我只好尝试用C#操作EXCEL类，将DATAVIEW的数据写入EXCEL，然后再生成柱状图，花了2天时间找资料，现在可以生成图了，但是生成的图不好看，细节上无法控制。
    我把生成图的代码贴下，我再附上XML方式出EXCEL的代码，只是没办法出图，而且设置文件头为自己想要的格式比较麻烦，要一行行写，还是EXCEL类库方式好，灵活方便，美观。
protected void exportdt_Click(object sender, EventArgs e)
    {
        //以下是用MS.Excel类库操作excel工作簿方式输出gridview数据并做图
        Excel.Application app = new Excel.Application();
        if (app == null)
        {
            return;
        }
        //以下是EXCEL.APPLICATION控制EXCEL方法
        app.Visible = true; //如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
        app.UserControl = true;
        app.DisplayAlerts=false;
        
        Excel.Workbooks workbooks = app.Workbooks;        //Excel.Workbook workbook = workbooks.Add(Type.Missing); //根据模板产生新的workbook
        Excel.Workbook workbook = workbooks.Open(@"D:\eaexcel\ea.xls", Type.Missing,
                        false,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing,
                        Type.Missing
        ); //或者根据绝对路径打开工作簿文件a.xls        stringinit();
        Excel.Sheets sheets = workbook.Worksheets;
        Excel._Worksheet worksheet = (Excel._Worksheet)sheets.get_Item(1);
        if (worksheet == null)
        {
            return;
        }        worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, FieldsSelectListBox.Items.Count + 2]).Merge(Type.Missing);//记号A
        worksheet.Cells[1,1] = "本表共输入企业" + dataview.Rows.Count + "家；时间：" + starttime.Text.Trim() + "，数据来源：" + TableList.SelectedItem.Text.Trim() + "，依据：" + FieldNameLabel.Text.Trim();
        for (int i = 0; i < FieldsSelectListBox.Items.Count ; i++)
        {
            worksheet.Cells[2, i+1] = FieldsSelectListBox.Items[i].Text.Trim();
        }
        worksheet.Cells[2, FieldsSelectListBox.Items.Count + 1] = "所属分类区间";//FieldsSelectListBox这个控件中放了分析数据做聚类分析的N个分类区间的中文名（为了让用户看名字就知道这类的意义）
        worksheet.Cells[2, FieldsSelectListBox.Items.Count + 2] = "所属区间分类条件";//给高级用户看的，因为允许用户自定特定的分类条件，显示出条件的语句，以便高级用户能纠正错误
        worksheet.Cells[2, FieldsSelectListBox.Items.Count + 3] = "分类区间序号";
        worksheet.Cells[2, FieldsSelectListBox.Items.Count + 4] = "所属分类区间";//这行和下行代码生成的数据区域就是EXCEL生成柱状图的数据源
        worksheet.Cells[2, FieldsSelectListBox.Items.Count + 5] = "企业数量";
        
        string strQuery = "select * from  sorttmp order by sortscript";
        string strQuery2 = "select * from  sortcount order by scnum";
        //下面是利用QueryTable想EXCEL导出大量数据的方式，效率高，我试过其他方式都不理想
        Excel._QueryTable qtb = worksheet.QueryTables.Add(@"OLEDB;Provider=SQLOLEDB.1;Data Source=84EBE6E6C5D9460\LOCALHOST;Password=sa;User ID=sa;Initial Catalog=economicanalyse;", worksheet.get_Range("A3", Type.Missing), strQuery);
        qtb.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows;
        qtb.FieldNames = false;
        qtb.Refresh(false);
        Excel._QueryTable qtb2 = worksheet.QueryTables.Add(@"OLEDB;Provider=SQLOLEDB.1;Data Source=84EBE6E6C5D9460\LOCALHOST;Password=sa;User ID=sa;Initial Catalog=economicanalyse;", worksheet.get_Range((char)((int)'A' + FieldsSelectListBox.Items.Count + 2) + "3", Type.Missing), strQuery2);//写到这句的时候很郁闷，为什么记号A这句get_Range可以用2个单元格CELL表示，但是这里就只能用A3,B4之类的行列序号表示了，用记号A的方式就出错，只好用这个方法了，有直接的方法一定要告诉我哦
        qtb2.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows;
        qtb2.FieldNames = false;
        qtb2.Refresh(false);        //下面是画图表了
        workbook.Charts.Add(Type.Missing,Type.Missing,1,Type.Missing);
        workbook.ActiveChart.ChartType = Excel.XlChartType.xlColumnClustered;//定义图类型
        workbook.ActiveChart.SetSourceData(worksheet.get_Range((char)((int)'A' + FieldsSelectListBox.Items.Count + 3) + "3", (char)((int)'A' + FieldsSelectListBox.Items.Count + 4) +Convert.ToString(GroupFSptListBox.Items.Count+2)), Excel.XlRowCol.xlRows);//图数据源
        workbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");//表示图示画在SHEET1的，改成自己的SHEET名就好
        workbook.ActiveChart.HasTitle=true;//没有这个标题就出不来开始我没加这个标题就是出不来
        workbook.ActiveChart.ChartTitle.Text = TableList.SelectedItem.Text.Trim() + "分类图";
        workbook.ActiveChart.ChartTitle.AutoScaleFont = false;//这个也要不然字体大小就默认自动缩放就不是14了
        workbook.ActiveChart.ChartTitle.Font.Size = 14;
        workbook.ActiveChart.ChartTitle.Font.Bold = true;//加粗
        Excel.Axis xAxis = (Excel.Axis)workbook.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);//定义X轴
        xAxis.Delete();  //这个也琢磨了好一会，因为有了这个下面会多个数字1，我开始想不写上面那句定义X轴的是不是就没了，但是发现还是一样，结果就用了这个，主坐标轴无X轴，果然下面光秃秃了，好看多了哈
        Excel.Axis yAxis = (Excel.Axis)workbook.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
        yAxis.HasTitle = true;
        yAxis.AxisTitle.AutoScaleFont = false;  //不关掉自动缩放的话后面的字体大小无法设置
        yAxis.AxisTitle.Font.Size=12;  //Y轴标题字体大小
        yAxis.AxisTitle.Text = FieldNameLabel.Text.Trim();
        yAxis.TickLabels.AutoScaleFont = false;
        yAxis.TickLabels.Font.Size = 12;  //Y轴坐标轴字体大小
        Excel.Range oResizeRange;
        oResizeRange = (Excel.Range)worksheet.Rows.get_Item(GroupFSptListBox.Items.Count+3, Type.Missing);
        worksheet.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;  //调图表的位置上边距
        oResizeRange = (Excel.Range)worksheet.Columns.get_Item(FieldsSelectListBox.Items.Count+3, Type.Missing);  
        worksheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;//调图表的位置左边距        workbook.ActiveChart.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowValue, false, false, false, false, false, true, false, false, false);//设置绘图区的数据标志（就是柱子顶上出现值）显示出值来，我这里就是数量
        Excel.Series oSeries;//实例化Series类
        Excel.DataLabels oDLabels ;//实例化DataLabels类，查找DataLabels.font,发现只有Excel.DataLabels有这个属性，问题之所在
        
        
        for (int i = 0; i < ZoneListBox.Items.Count;i++ ) 
        {
            oSeries = (Excel.Series)workbook.ActiveChart.SeriesCollection(i+1);
            oSeries.HasDataLabels = true;//每根柱子都有数据标签
            oSeries.Border.ColorIndex = 45;//每根柱子边框颜色是黄色（不是指柱子的颜色哦，柱子颜色系统自动区别开的，要自定义也行，但最好别给自己找麻烦），不同的数字代表不同的颜色
                        //oDLabels = (Excel.DataLabels)(((Excel.Series)workbook.ActiveChart.SeriesCollection(i + 1)).DataLabels(i+1));//郁闷说我接口不对
            //oDLabels.Font.Size = 11;//上面一行过不了，这行就不能设置标签字体大小，结果出来的图标签默认字体太大，当柱子多的时候，因为柱子不够宽，顶上的数据标签会重叠到一起，没法看了
        }
                worksheet.Shapes.Item("Chart 1").Width =ZoneListBox.Items.Count * 20 + (int)(workbook.ActiveChart.Legend.Width) + 200;   //调图表的宽度，这样根据柱子的数量乘以一个宽度值再加上图例的宽度再加上余量（左右边距什么的）出来的图就不会太小了，基本上不错了，稍微放大缩小下就好，非常方便
        worksheet.Shapes.Item("Chart 1").Height = ZoneListBox.Items.Count * 10+200;  //调图表的高度        workbook.ActiveChart.PlotArea.Width = ZoneListBox.Items.Count * 20;   //设置绘图区宽度
        workbook.ActiveChart.PlotArea.Top = 20;
        workbook.ActiveChart.PlotArea.Height = ZoneListBox.Items.Count * 10;  //设置绘图区高度
        workbook.ActiveChart.PlotArea.Left = 20;        //设置Legend图例的位置和格式
        workbook.ActiveChart.HasLegend = true;
        workbook.ActiveChart.Legend.AutoScaleFont = false;
        workbook.ActiveChart.Legend.Top = 10; //具体设置图例的上边距
        //workbook.ActiveChart.Legend.Left = 450;//具体设置图例的左边距
        workbook.ActiveChart.Legend.Font.Size = 9.5;
        workbook.ActiveChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight;        //workbook.ActiveChart.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 49, 260, 293, 43);//郁闷之所在，我用录制宏的办法在VBA代码里看到就是这么做的，怎么我用C#这么做就没有文本框出来呢，能出文本框的话，我就想在绘图区下面（X轴给我关掉了）空白的地方加上文本框，里面做些有意义的说明给初级用户看        //以下是保存EXCEL文件
        string fname = TableList.SelectedItem.Text.Trim() + DateTime.Today.ToString("yyyyMMdd") + new Random(DateTime.Now.Millisecond).Next(10000).ToString() + ".xls";
        workbook.SaveCopyAs(Server.MapPath(". ") +  "\\" + fname.ToString());
        workbook.Close(Type.Missing, Type.Missing, Type.Missing);
        workbooks.Close();
        app.Quit();
        workbook = null;
        workbooks = null;
        app = null;
        GC.Collect();
        string path = Server.MapPath(fname.ToString());
        System.IO.FileInfo file = new System.IO.FileInfo(path.ToString().Trim());
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312 ";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        //添加头信息，为 "文件下载/另存为 "对话框指定默认文件名   
        Response.AddHeader("Content-Disposition","attachment;filename="+HttpUtility.UrlEncode(file.FullName,System.Text.Encoding.UTF8));           //添加头信息，指定文件大小，让浏览器能够显示下载进度   
        Response.AddHeader("Content-Length ", file.Length.ToString());   
        Response.ContentType = "application/octet-excel ";
        //把文件流发送到客户端   
        Response.WriteFile(file.FullName);
        Response.Flush();
        //停止页面的执行   
        Response.End(); 
    }
     总结下我的问题：1，为什么很多场合下比如worksheet.QueryTables.Add方法和workbook.ActiveChart.SetSourceData方法里面都有参数get_range,这些情况下一定要用get_range（"A3","C5"）这样的，你用get_range（worksheet.Cells[3, 1], worksheet.Cells[5, 3])就会出错；
                     2，到底怎么定义数据标签DataLabels的属性，比如字体、大小之类，因为数据标签可能比较多，你出图以后一个个手工改太累了；
                     3，如何在图中间添加文本框以及定义它的属性。

解决方案 »

  1.   

        刚才说我内容太长，只好再分开发XML方式的了
    //public override void VerifyRenderingInServerForm(Control control)//XML方式这个一定不能少
        //{
       //}    //public void gv_RowCreated(object sender, GridViewRowEventArgs e)//在gridview以XML方式输出的时候初始化表头以达到用户想要的显示效果
        //{
        //    if (e.Row.RowType == DataControlRowType.Header)
        //    {
        //        TableCellCollection tcHeader = e.Row.Cells;
        //        tcHeader.Clear();//原有表头清空
        //        tcHeader.Add(new TableHeaderCell());
        //        tcHeader[0].Attributes.Add("colspan", Convert.ToString(FieldsSelectListBox.Items.Count + 2));
        //        tcHeader[0].Text = "本表共输入企业" + dataview.Rows.Count + "家；时间：" + starttime.Text.Trim() + "，数据来源：" + TableList.SelectedItem.Text.Trim() + "，依据：" + FieldNameLabel.Text.Trim() + "</th></tr><tr class='tr_5px'>";
        //        tcHeader[0].Width = (FieldsSelectListBox.Items.Count + 2) * 120;    //        for (int i = 0; i < FieldsSelectListBox.Items.Count; i++)
        //        {
        //            tcHeader.Add(new TableHeaderCell());
        //            tcHeader[i + 1].Text = FieldsSelectListBox.Items[i].Text.Trim();
        //            tcHeader[i + 1].Width = 120;
        //        }
        //        tcHeader.Add(new TableHeaderCell());
        //        tcHeader[FieldsSelectListBox.Items.Count +1].Text = "所属区间";
        //        tcHeader[FieldsSelectListBox.Items.Count + 1].Width = 120;    //        tcHeader.Add(new TableHeaderCell());
        //        tcHeader[FieldsSelectListBox.Items.Count + 2].Text = "所属区间分类条件";
        //        tcHeader[FieldsSelectListBox.Items.Count + 2].Width = 120;
        //    }
        //}
    protected void exportdt_Click(object sender, EventArgs e)
        {
            //以下是把gridview以xml方式输出为excel文件的代码
            //Response.Clear();
            //Response.Buffer = true;
            //Response.Charset = "GB2312";
            //Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
            //// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
            //Response.ContentEncoding = System.Text.Encoding.UTF7;
            //Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
            //System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            //System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);        //stringinit();
            //DataSet ds1 = Deptdata.GetSortData(dataviewfields.ToString().Trim(), sortexpression.ToString().Trim(), sortscript.ToString().Trim(), tablename, starttime.Text.Trim());
            //DataSet ds2 = Deptdata.GetSorttmpData("sorttmp");
            //GridView gv = new GridView();
            //gv.RowCreated += new System.Web.UI.WebControls.GridViewRowEventHandler(this.gv_RowCreated);
            //gv.DataSource = ds2;
            //gv.DataBind();
            //gv.RenderControl(oHtmlTextWriter);
            //Response.Output.Write(oStringWriter.ToString());
            //Response.Flush();
            //Response.End();        //以下是用MS.Excel类库操作excel工作簿方式输出gridview数据并做图
    ……………………
      

  2.   

    问题1，2已经自己解决，问题3还是不行，workbook.ActiveChart.Shapes.AddTextbox（）之后，发现文本框是看不到的，怎么回事？有么有达人啊？
      

  3.   

    加Textbox后是否还要设置大小等，另外文本的内容要设置吧
      

  4.   


    // Excel 可以直接打开的
    DLExitFormManage efm = new DLExitFormManage();
    DataTable dt = efm.GetDLNoNormalExitListDetail("", 1); // 得到要导出的数据
    StringWriter sw = new StringWriter();
    sw.WriteLine("部门\t姓名\t英文名\t工号\t性别\t入职日");
    foreach (DataRow dr in dt.Rows)
    {
        sw.WriteLine(dr["deptcode"] + "\t" + dr["empcname"] + "\t" + dr["empename"] + "\t" + dr["empid"] + "\t" + dr["gender"] + "\t" +
                        dr["entrydate"]);
    }
    sw.Close();
    Response.AddHeader("Content-Disposition", "attachment;   filename=ExitListDetail.xls");
    Response.ContentType = "application/ms-excel";
    Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
    Response.Write(sw);
    Response.End();
      

  5.   

     //oDLabels = (Excel.DataLabels)(((Excel.Series)workbook.ActiveChart.SeriesCollection(i + 1)).DataLabels(i+1));//郁闷说我接口不对
      //oDLabels.Font.Size = 11;//上面一行过不了，这行就不能设置标签字体大小，结果出来的图标签默认字体太大，当柱子多的时候，因为柱子不够宽，顶上的数据标签会重叠到一起，没法看了这个问题 解决了？？ 我遇到这个问题 找不到解决方法
    能 发下解决方案吗  邮箱 [email protected]
      

  6.   

    楼主，能把C#生成Excel的三种形式的报表的源码发我一份学习下不？
    邮箱 [email protected]