By RUKpapayday loans uk
I recently had to create a simple application at work. The user needed some data dumped into Excel each day so that another system could consume it. It sounded like a 2 minute job. Exporting data to CSV is easy… but when they said Excel they did actually mean the XLS file format. Again, not a big problem. I could easily attach to the Excel COM objects. Piece of cake. However, for the application to run daily I’d need to install and schedule it on a server. The servers don’t have Excel installed so there’s no COM objects to attach to.
After a brief ‘google’ I found the ExcelLibrary (excellibrary.googlecode.com). It creates XLS files without the need of the Excel objects. So here’s a cut down example of the C# code I had to create.
using ExcelLibrary;
static void CreateSpreadsheet(MySqlCommand cmd, string fileName, string sheetName) {
try
{
//connect to the database
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connectionString);
conn.Open();
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
//setup the xls file format
string xlFile = fileName;
string xlSheet = sheetName;
ExcelLibrary.SpreadSheet.Workbook wb = new ExcelLibrary.SpreadSheet.Workbook();
ExcelLibrary.SpreadSheet.Worksheet ws = new ExcelLibrary.SpreadSheet.Worksheet(xlSheet);
ws.SheetType = ExcelLibrary.SpreadSheet.SheetType.Worksheet;
//add the header row
foreach (DataColumn col in ds.Tables[0].Columns)
{
ws.Cells[0, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(col.ColumnName);
}
//add contentInt32 i = 1;
foreach (DataRow row in ds.Tables[0].Rows)
{
foreach (DataColumn col in ds.Tables[0].Columns)
{
//add the data
//impose strict formating on the cells rather than use defaults
if (row[col.Ordinal] != DBNull.Value || row[col.Ordinal].ToString()!="")
{
switch (col.DataType.ToString())
{
case "System.Char":
case "System.String":
ws.Cells[i, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(row[col.Ordinal].ToString());
break;
case "System.DateTime":
ws.Cells[i, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(Convert.ToDateTime(row[col.Ordinal]).ToString("d"));
break;
case "System.Int16":
case "System.Int32":
ws.Cells[i, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(Convert.ToInt32(row[col.Ordinal]));
break;
case "System.Int64":
ws.Cells[i, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(Convert.ToInt32(row[col.Ordinal]));
break;
case "System.Decimal":
case "System.Double":
case "System.Single":
ws.Cells[i, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(Convert.ToDecimal(row[col.Ordinal]));
break;
default:
ws.Cells[i, col.Ordinal] = new ExcelLibrary.SpreadSheet.Cell(row[col.Ordinal].ToString());
break;
}
}
}
i++;
}
wb.Worksheets.Add(ws);
wb.Save(xlFile);
//tidy up
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
The ExcelLibrary code has proved very easy to use. I could write a basic application in no time. The Library seems to run quite fast (I haven’t tried with large volumes of data…but you wouldn’t want that with Excel anyway).