CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Location
    Ohio
    Posts
    13

    [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.

  2. #2
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    130

    Re: Excel CSV Import Export Printing

    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!

  3. #3
    Join Date
    Jul 2012
    Location
    Ohio
    Posts
    13

    Re: Excel CSV Import Export Printing

    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)

  4. #4
    Join Date
    Jul 2012
    Location
    Ohio
    Posts
    13

    Re: Excel CSV Import Export Printing

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

  5. #5
    Join Date
    Jul 2012
    Location
    Ohio
    Posts
    13

    Re: Excel CSV Import Export Printing

    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.

  6. #6
    Join Date
    Jul 2012
    Location
    Ohio
    Posts
    13

    Re: Excel CSV Import Export Printing

    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*

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured