excelexport.cs

cics文本日志分析

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using Excel;

namespace Txt2Sheet.myUtil
{
    public class ExcelExport
    {
   
        private object _optionalValue = System.Reflection.Missing.Value;

        // Export to Excel if it is installed on their machine
        public void ExportData(TxtData myData)
        {
            Excel.Application excelApp;
            Excel.Workbook workBook;
            Excel.Worksheet ws;
            Excel.Range r;

            int row = 1;
            int cell = 1;
            int priorityHigh, priorityNormal, priorityLow;

            /*try
            {
                // which ever version of Excel was referenced to build the interop file
                // should be installed on client machines
                excelApp = new Excel.Application();

                // create the task list on the first worksheet in the workbook
                workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                ws = (Worksheet)workBook.Worksheets[1];
            }
            catch
            {
                // remove the excel references: Excel, Microsoft.Office.Core, and VBIDE and re-add
                // the COM reference Microsoft Excel 10.0 Type Library.
                MessageBox.Show("An incompatible version of Microsoft Excel is intalled on your system.");
                return;
            }

            // Title
            ws.Name = "Task Management";
            ws.Cells[row, 1] = "Tasks as of " + DateTime.Now.ToShortDateString();
            r = (Range)ws.Cells[row, 1];
            r.Font.Bold = true;
            row += 2;

            // create Headers
            foreach (DataColumn dc in TasksData.Tables[0].Columns)
            {
                foreach (DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
                {
                    if (cs.Width != 0)
                    {
                        if (cs.MappingName == dc.ColumnName)
                        {
                            ws.Cells[row, cell] = cs.HeaderText;

                            // Autofit the column: unusual C# interface: use Columns indexer, 
                            // pass in column index as first parameter (row index), optional value 
                            // as second parameter (column index), and it uses the first parameter as column index.
                            r = (Range)ws.Columns[cell, _optionalValue];
                            r.AutoFit();
                            break;
                        }
                    }
                    cell++;
                }
                cell = 1;
            }
            row++;

            // create rows
            priorityHigh = 0;
            priorityNormal = 0;
            priorityLow = 0;

            int i;

            for (i = 0; i < TasksDataView.Count; i++)
            {
                foreach (DataColumn dc in TasksData.Tables[0].Columns)
                {
                    foreach (DataGridColumnStyle cs in TasksGrid.TableStyles[0].GridColumnStyles)
                    {
                        if (cs.Width != 0)
                        {
                            if (cs.MappingName == dc.ColumnName)
                            {
                                if (TasksDataView[i][dc.ColumnName.ToString()] is DateTime)
                                    ws.Cells[row, cell] = String.Format("{0:d}", TasksDataView[i][dc.ColumnName.ToString()]);
                                else
                                    ws.Cells[row, cell] = TasksDataView[i][dc.ColumnName.ToString()];

                                if (cs.MappingName == "PriorityText")
                                {
                                    switch (TasksDataView[i][dc.ColumnName.ToString()].ToString())
                                    {
                                        case "Major":
                                            priorityHigh++;
                                            break;

                                        case "Medium":
                                            priorityNormal++;
                                            break;

                                        case "Minor":
                                            priorityLow++;
                                            break;
                                    }
                                }
                                // Do the AutoFit C# column call again:
                                r = (Range)ws.Columns[cell, _optionalValue];
                                r.AutoFit();
                                break;
                            }
                        }
                        cell++;
                    }
                    cell = 1;
                }
                row++;
            }

            // Priority Summary and Chart on Second Worksheet
            Excel.Worksheet ws2 = (Worksheet)workBook.Worksheets.Add(_optionalValue, ws, 1, XlWBATemplate.xlWBATWorksheet);
            ws2.Name = "Priority Summary";

            row = 1;
            ws2.Cells[row, 1] = "Priority Summary";
            r = (Range)ws.Cells[row, 1];
            r.Font.Bold = true;
            row += 2;
            ws2.Cells[row, 1] = "Major";
            ws2.Cells[row, 2] = priorityHigh.ToString();
            row++;
            ws2.Cells[row, 1] = "Medium";
            ws2.Cells[row, 2] = priorityNormal.ToString();
            row++;
            ws2.Cells[row, 1] = "Minor";
            ws2.Cells[row, 2] = priorityLow.ToString();

            // AutoFit both columns:
            r = (Range)ws2.Columns[1, _optionalValue];
            r.AutoFit();
            r = (Range)ws2.Columns[2, _optionalValue];
            r.AutoFit();

            // Create the chart--this is quite different than the VB.NET version
            r = ws2.get_Range("A3", "B5");
            r.Select();

            Chart newChart = (Chart)workBook.Charts.Add(_optionalValue, ws, _optionalValue, _optionalValue);

            newChart.ChartType = XlChartType.xl3DPieExploded;
            newChart.SetSourceData(r, XlRowCol.xlColumns);
            newChart.Name = "Priority Summary Chart";
            newChart.HasTitle = true;
            newChart.ChartTitle.Text = "Tasks by Priority";
            newChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws2.Name);

            // Show(Excel)
            ws.Activate();
            excelApp.Visible = true;*/
        }
    }
}