|
-
July 18th, 2012, 12:33 PM
#2
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!
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|