txt导出到Excel（数据和图） C#

来源：互联网发布：js confirm 修改按钮编辑：程序博客网时间：2024/06/05 07:37

一、从txt文件中读取数据

StreamReader read = new StreamReader(strFilePath);

if (read == null)

string strTxt = read.ReadToEnd();

其中strTxt为txt文件内容；这种读取效率比逐行读取高些；

二、将数据存入DateTable中

此处省略，根据具体需求处理字符串；

三、存入Excel中

1、创建Excel

            xslApp = new Microsoft.Office.Interop.Excel.Application();
            xslApp.SheetsInNewWorkbook = 5;
            xslApp.Workbooks.Add();
            xslApp.Visible = true;  //是否显示Excel

workbook.Saved = true; //是否保存

System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

2、导出数据

object[,] data = new object[dt.Rows.Count, 11];
int count = 0;

                for (int i = 0; i < dt.Rows.Count;i++ )
                {
                    System.Data.DataRow dr = dt.Rows[i];
                    for (int j = 0; j < dt.Columns.Count - 1; j++)
                    {
                                data[count, j] = "'" + dr[j];
                    }
                    count++;
                }

                //填充数据
                Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range("A1", "K" + (dt.Rows.Count + 1).ToString());
                rang = rang.get_Resize(dt.Rows.Count, 11);
                rang.set_Value(System.Reflection.Missing.Value, data);

这种填充方式比逐个单元格填充的效率高很多；

3、绘制图形

        public static void DrawLinePic(Microsoft.Office.Interop.Excel.Workbook workbook,
           Microsoft.Office.Interop.Excel.Worksheet worksheet, ExcelPapamMolde papam, int index,
           System.Data.DataTable dt)
        {
            if (workbook == null)
                return;

            //轴标题偏移量
            const int m_titleOffset = 20;
            //CHART的范围偏移
            const int CHARTAREAEXCURSION = 10;
            //设置绘图区宽度
            const int PLOTAREAWIDTH = 850;
            //X轴标题字体大小
            const int XAXISTITLEFONTSIZE = 10;
            //X轴坐标轴字体大小
            const int XAXISTICKLABELSFONTSIZE = 10;
            //Y轴标题字体大小
            const int YAXISTITLEFONTSIZE = 10;
            //Y轴坐标轴字体大小
            const int YAXISTICKLABELSFONTSIZE = 10;
            //ChartGroup的宽度
            const int CHARTGROUPWIDTH = 100;
            try
            {
                //将图表插入指定的位置
                ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[index]).Activate(); //将折线图插入制定index位置，sheet的位置
                workbook.Charts.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                workbook.ActiveChart.ChartType = XlChartType.xlLine; //图标类型，折现图，可根据需要修改

Microsoft.Office.Interop.Excel.Range rang = null;
int count = dt.Rows.Count > 4000 ? 4000 : papam.ValueendRow;

string str1 = "A1:D10"; //获取不连续数据块作为数据源，可多个
string str2 = "F1:K12";

rang = worksheet.get_Range(str1 + "," + str2);
workbook.ActiveChart.SetSourceData(rang, XlRowCol.xlColumns);

                //图形宽和高
                workbook.ActiveChart.ChartArea.Width = papam.PicWidth;
                workbook.ActiveChart.ChartArea.Height = papam.PicHeight;

//表示图示画在SHEET1的，改成自己的SHEET名就好
workbook.ActiveChart.Location(XlChartLocation.xlLocationAutomatic, papam.PageName);

                //没有这个标题就出不来
                workbook.ActiveChart.HasTitle = true;
                //报表名称
                workbook.ActiveChart.ChartTitle.Text = papam.ChartName;

workbook.ActiveChart.ChartArea.Width = papam.PicWidth + CHARTAREAEXCURSION;
workbook.ActiveChart.ChartArea.Height = papam.PicHeight + CHARTAREAEXCURSION;

                //图形距离左上角的距离
                workbook.ActiveChart.ChartArea.Top = papam.Top;
                workbook.ActiveChart.ChartArea.Left = papam.Left;

                #region - 定义绘图区 -
                //设置绘图区的背景色
                workbook.ActiveChart.PlotArea.Interior.ColorIndex = ColorIndex.LightViridity;
                //设置绘图区边框线条
                workbook.ActiveChart.PlotArea.Border.LineStyle = XlLineStyle.xlLineStyleNone;
                //设置绘图区宽度
                workbook.ActiveChart.PlotArea.Width = PLOTAREAWIDTH;
                #endregion

                #region - 定义X轴 -
                //轴样式
                Axis xAxis = (Axis)workbook.ActiveChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                //x轴显示值的范围
                xAxis.CategoryNames = worksheet.get_Range( "A1","D1");

                xAxis.HasTitle = true;
                xAxis.AxisTitle.AutoScaleFont = false; //不关掉自动缩放的话后面的字体大小无法设置
                xAxis.AxisTitle.Font.Size = XAXISTITLEFONTSIZE; //X轴标题字体大小
                xAxis.AxisTitle.Text = papam.XAxisName;//X轴名
                xAxis.TickLabels.AutoScaleFont = false;
                xAxis.TickLabels.Font.Size = XAXISTICKLABELSFONTSIZE; //X轴坐标轴字体大小
                xAxis.AxisTitle.Left = papam.PicWidth - m_titleOffset;

#endregion

#region - 定义Y轴 -

Axis yAxis = (Axis)workbook.ActiveChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);

                yAxis.HasTitle = true;
                yAxis.AxisTitle.AutoScaleFont = false; //不关掉自动缩放的话后面的字体大小无法设置
                yAxis.AxisTitle.Font.Size = YAXISTITLEFONTSIZE; //Y轴标题字体大小
                yAxis.AxisTitle.Text = papam.YAxisName;//Y轴名
                yAxis.TickLabels.AutoScaleFont = false;
                yAxis.TickLabels.Font.Size = YAXISTICKLABELSFONTSIZE; //Y轴坐标轴字体大小
                yAxis.AxisTitle.Top = m_titleOffset;
                yAxis.MaximumScale = 120;  //Y轴的取值范围
                yAxis.MinimumScale = 20;
                #endregion

                //设置绘图区的数据标志（就是线形顶上出现值）显示出值来
                //workbook.ActiveChart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, false
                //     , false, false, false, true, false, false, false);

                ChartGroup grp = (ChartGroup)workbook.ActiveChart.ChartGroups(1);
                grp.GapWidth = CHARTGROUPWIDTH;
                grp.SizeRepresents = XlSizeRepresents.xlSizeIsWidth;
                workbook.ActiveChart.PlotArea.Width = papam.PicWidth - m_titleOffset; //设置绘图区宽度
                workbook.ActiveChart.PlotArea.Top = m_titleOffset;
                workbook.ActiveChart.PlotArea.Height = papam.PicHeight - m_titleOffset; //设置绘图区高度
                workbook.ActiveChart.PlotArea.Left = m_titleOffset;

                //设置Legend图例的位置和格式
                workbook.ActiveChart.HasLegend = true;
                workbook.ActiveChart.Legend.Left = 650;
                workbook.ActiveChart.Legend.Top = 30;

            }
            catch (Exception ex)
            { }
        }

4、保存

workbook.SaveCopyAs(strPath);