[RESOLVED] Excel CSV Import Export Printing

I am working on my first Import Export manager. I keep looking at the objects and I don't quite understand how to go from csv to excel to a nice printable format. Used the Jet engine and a schema.ini file to import export my csv's is there something along those lines I could do for the excel portion and formatting? I have done googles, and checked the MSDN quite a bit. I keep coming back to Interop but I can't get this to work write on my Development machine, and it looks like it might require office 2010. I only have office 2003 to work with. I also need this program to be compatible with both Win7 and WinXP. I think I am looking for example codes and reference that I may not have found.

My initial thought was this should be cake because when I open a csv it opens in excel formatted the way I want anyway so just printing it from there would be ideal. Unfortunately this can of worms is deeper than I thought.

It took me a while to get this working, and a lot of Excel's capabilities are not implemented, but this should help you with the exporting at least.
The classes in the Excel environment don't expose very friendly when typing stuff in VS so you don't have a clue if what you're writing will even exist! You'll probable get a few runtime errors because of that.

Code:

---

// Add a reference in your Project to Microsoft.Office.Interop.Excel

using System;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

static class Excel
{
        private const int TITULOS_START_ROW = 2;
        private const int TITULOS_START_COL = 2;
        private const int DATOS_START_ROW = TITULOS_START_ROW + 2;
        private const int DATOS_START_COL = TITULOS_START_COL + 0;

        public static string Error = "No error";

        public static bool Export(List<string> Titulos, List<string> Nombres, int pCount, string[] fechas, string[,] valores, bool[] validez)
        {
                int i, j;

                // Inicio la aplicación Excel
                Application xlApp = new Application();

                if (xlApp == null)
                {
                        Error = "Excel no pudo ser iniciado. Verifique la instalación del paquete de Office y vuelva a intentarlo.";
                        return false;
                }
                xlApp.Visible = true;

                // Creo una nueva hoja de trabajo
                Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet ws = (Worksheet)wb.Worksheets[1];

                if (ws == null)
                {
                        Error = "No se pudo crear una página. Verifique la instalación del paquete de Office y vuelva a intentarlo.";
                        return false;
                }

                ws.Name = "Reporte OnLine";

                // Agrego la lista de tÃ*tulos (comentarios) iniciales
                for (i = 0; i < Titulos.Count; i++)
                        ws.Cells[TITULOS_START_ROW + i, TITULOS_START_COL] = Titulos[i];

                // Agrego los titulos de las columnas de datos
                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL] = "Fecha";
                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL].Font.Bold = true;
                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL].EntireColumn.ColumnWidth = 17;
                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL].HorizontalAlignment = XlHAlign.xlHAlignCenter;

                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL + Nombres.Count + 1] = "Valido?";
                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL + Nombres.Count + 1].Font.Bold = true;
                ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL + Nombres.Count + 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;

                for (i = 0; i < Nombres.Count; i++)
                {
                        ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL + i + 1] = Nombres[i];
                        ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL + i + 1].Font.Bold = true;
                        ws.Cells[DATOS_START_ROW + Titulos.Count, DATOS_START_COL + i + 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                }

                // Agrego todos los datos de fecha, valores y validez
                for (i = 0; i < pCount; i++)
                {
                        ws.Cells[DATOS_START_ROW + Titulos.Count + i + 1, DATOS_START_COL] = fechas[i];
                        ws.Cells[DATOS_START_ROW + Titulos.Count + i + 1, DATOS_START_COL].NumberFormat = "DD/MM/YYYY HH:mm";
                        for (j = 0; j < Nombres.Count; j++)
                                ws.Cells[DATOS_START_ROW + Titulos.Count + i + 1, DATOS_START_COL + j + 1] = double.Parse(valores[j, i]);
                        ws.Cells[DATOS_START_ROW + Titulos.Count + i + 1, DATOS_START_COL + Nombres.Count + 1] = validez[i] ? "Si" : "No";
                        ws.Cells[DATOS_START_ROW + Titulos.Count + i + 1, DATOS_START_COL + Nombres.Count + 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                }

                string topLeft = GetExcelColumnName(DATOS_START_COL) + (DATOS_START_ROW + Titulos.Count + 1).ToString();
                string bottomRight = GetExcelColumnName(DATOS_START_COL) + (DATOS_START_ROW + Titulos.Count + pCount).ToString();
                Range xValues = ws.get_Range(topLeft, bottomRight);

                // Agrego un gráfico por cada consituyente
                for (i = 0; i < Nombres.Count; i++)
                {
                        topLeft = GetExcelColumnName(DATOS_START_COL + i + 1) + (DATOS_START_ROW + Titulos.Count + 1).ToString();
                        bottomRight = GetExcelColumnName(DATOS_START_COL + i + 1) + (DATOS_START_ROW + Titulos.Count + pCount).ToString();

                        ChartObjects charts = (ChartObjects)ws.ChartObjects();
                        ChartObject chartObject = (ChartObject)charts.Add(60 * (Nombres.Count + 4), 300 * i + 50, 800, 300);
                        Chart chart = chartObject.Chart;
                        chart.ChartType = XlChartType.xlLineMarkers;
                        chart.HasLegend = false;

                        Range yValues = (Range)ws.get_Range(topLeft, bottomRight);

                        SeriesCollection seriesCollection = chart.SeriesCollection();

                        Series XYValues = seriesCollection.NewSeries();
                        XYValues.XValues = xValues;
                        XYValues.Values = yValues;

                        chart.ChartWizard(Title: Nombres[i], CategoryTitle: "Fecha", ValueTitle: "Valor");
                }

                xlApp.Quit();
                return true;
        }
        private static string GetExcelColumnName(int columnNumber)
        {
                string columnName = String.Empty;
                int dividend = columnNumber;
                int modulo;

                while (dividend > 0)
                {
                        modulo = (dividend - 1) % 26;
                        columnName = Convert.ToChar('A' + modulo).ToString() + columnName;
                        dividend = (int)((dividend - modulo) / 26);
                }

                return columnName;
        }
}

---

Comments, names and variables are in spanish and is adapts to a very specific need for my software, but it should give you enough info to make your own.

Hope it helps!

Getting Microsoft.Office.Interop.Excel to work notes:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 800702e4 The requested operation requires elevation. (Exception from HRESULT: 0x800702E4). <-- ERROR


Things I've Already Tried:
Make Sure Set To .NET Framework 4 not Client Profile
Reinstalling Office Assembly
Re-Adding Assemblies to Project
Making sure project is set to x86
Registering DLL as x32
Turned Off UAC
Tried Direct Referencing in code
made new project and started over (since it's a test before implementing anyhow)

I finished Crystal Report Export Printer but still can't get this to work.

SOLVED (for now) Not the way I would like but I found a work around until I figure out that Interop problem. I used Crystal Reports to export the data into excel. It's dirty, but it works, for now. At least it's results I can move on and come back to my issue later.

Sorry Folks I made it back around to this issue, the only thing I can really tell you is that I think the System.Data was interfering in the Interop but that doesn't explain why it didn't work when I started a new project. It works now no problem *shrugs*