Visitas: 24208
Buscando por Internet una manera fácil de leer y escribir archivos Excel de forma automática he encontrado una clase muy útil. Es una manera muy fácil de conseguir hacer todas las tareas habituales con un archivo de Excel.
Para hacer que funcione debe agregar la referencia COM de Excel llamada “Biblioteca de objetos de Microsoft Excel 11.0″.
Espero que les resulta tan útil como lo ha sido para mí.
Nota: Si está utilizando VS2008 (o puede ser también otras versiones), debe reemplazar el método “getSheetsNames” por:
public string[] GetSheetsNames()
{
List
Worksheet sheet = null;
for (int i = 1; i <= this.book.Worksheets.Count; i++)
{
sheet = (Worksheet)this.book.Worksheets[i];
names.Add(sheet.Name);
}
return names.ToArray();
}
En caso contrario generará errores el objeto List.
// A library to handle excel files in a simple way.
// Copyright (C) 2009 Gorka Suárez García
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public License
// along with this program. If not, see .
using System;
using System.Collections.Generic;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
namespace Excel {
///
/// This class is used to handle an excel file to write and read from it.
/// Author: Gorka Suárez García
///
public class ExcelHandler {
///
/// The excel application instance.
///
private ApplicationClass app;
///
/// The excel book.
///
private Workbook book;
///
/// The path of the excel file.
///
private string path;
///
/// Constructs a new ExcelHandler object.
///
public ExcelHandler() {
this.app = null;
this.book = null;
this.path = null;
}
///
/// Destroys the ExcelHandler object.
///
~ExcelHandler() {
if (this.app != null) {
this.app.Quit();
}
}
///
/// Opens an excel file.
///
/// The file to open.
public void Open(string path) {
this.path = path;
this.app = new ApplicationClass();
this.app.Visible = false;
this.app.ScreenUpdating = false;
this.app.DisplayAlerts = false;
this.book = this.app.Workbooks.Open(this.path, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
if (this.book == null)
throw new Exception("Can't open the excel book file.");
}
///
/// Writes a value in a cell.
///
/// The sheet to write.
/// The cell to write.
/// The value to write.
public void Write(string sheet, string cell, string value) {
Worksheet wsheet = this.getSheet(sheet);
Range range = wsheet.get_Range(cell, cell);
range.Value2 = value;
}
///
/// Reads a value from a cell.
///
/// The sheet to read.
/// The cell to read.
/// The value from the cell.
public string Read(string sheet, string cell) {
Worksheet wsheet = this.getSheet(sheet);
Range range = wsheet.get_Range(cell, cell);
if (range.Value2 != null)
return range.Value2.ToString();
else
return "";
}
///
/// Clears the content of the excel book.
///
public void Clear() {
Worksheet sheet = null;
for (int i = 1; i <= this.book.Worksheets.Count; i++) {
sheet = (Worksheet)this.book.Worksheets[i];
sheet.Cells.Clear();
}
}
///
/// Closes the excel file.
///
public void Close() {
this.book.SaveAs(this.path, XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value,
false, false, XlSaveAsAccessMode.xlShared, false, false, Missing.Value,
Missing.Value, Missing.Value);
this.book.Close(true, Missing.Value, Missing.Value);
this.app.Quit();
this.app = null;
this.book = null;
this.path = null;
}
///
/// Gets all the names of the sheets inside the excel book.
///
/// A list of the sheets names.
public string[] GetSheetsNames() {
List names = new List();
Worksheet sheet = null;
for (int i = 1; i <= this.book.Worksheets.Count; i++) {
sheet = (Worksheet)this.book.Worksheets[i];
names.Add(sheet.Name);
}
return names.ToArray();
}
///
/// Gets a sheet we're looking for.
///
/// The name of the sheet.
/// The sheet we're looking for.
protected Worksheet getSheet(string name) {
int index = this.getSheetIndex(name);
if (index == 0)
throw new Exception("Invalid sheet name.");
Worksheet sheet = (Worksheet)this.book.Worksheets[index];
return sheet;
}
///
/// Gets the index of a sheet we're looking for.
///
/// The name of the sheet.
/// The index of the sheet we're looking for.
protected int getSheetIndex(string name) {
Worksheet sheet = null;
for (int i = 1; i <= this.book.Worksheets.Count; i++) {
sheet = (Worksheet)this.book.Worksheets[i];
if (sheet.Name == name) return i;
}
return 0;
}
}
}
