Image via WikipediaFaced many problems when loading or exporting to excel files! got many confused when loading data from excel file and it seems that the data you have is less than the data in the file. Needed to do many calculations and formatting!the problem is we don't know how to deal with excel, and how excel deals with data connectors like ODBC.
I faced these issues and searched a lot to get a result and got these guidelines.
1- for loading an excel file make sure you format the excel file to the desired format you want. example: if you only will get the data in number format, so format the column with the suitable numeric format. so that all the data that you will have is the numbers, if you types a word in this column, excel will not transfer this value to the ODBC driver.
If you want it generic so you will format it as text. so whatever gets into the cell excel will transfer it.
here is a snippet of how loading from excel. [the code is in C#]
public DataTable LoadSheet(string sheetName)
{
DataTable dtSheet = new DataTable();
string excelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";
string command = "select * from [" + sheetName + "$]";
new System.Data.OleDb.OleDbDataAdapter(command, excelConString).Fill(dtSheet);
return dtSheet;
}
this how to load a sheet of excel into DataTable which you can deal with in the rest of your code.
*Trick: when reading, excel considers the first formatted row as the table header [formatted means bordered] if you did not do that the headers will be F1, F2...etc
2- Writing to Excel. the problem begins.
- first you have to export to a named sheet
- the sheet should be in a table format, if you typed in this sheet before exporting the writing process will begin writing after your writing. example: you write in cell A2, when exporting the excel will begin writing from A3, B3,C3...etc.
- any formats should be applied on the previous cell that you want to write in. example: you to begin writing in cell A4, and you want it to be formated as numeric (#.00). So, you have to apply this format on cell A3 only and not the column.
- now you need the way to insert the rows.
connection.Open();
OleDbCommand command;
// insert rows
foreach (object item in items)
{
// insert row
command = CreateInsertCommand(sheetName, fields, connection, item);
command.ExecuteNonQuery();
}
private OleDbCommand CreateInsertCommand(string sheetName, ExcelField[] fields, OleDbConnection connection, object item)
{
StringBuilder builder = new StringBuilder();
string fieldValue;
OleDbParameter parameter = null;
int count = fields.Length;
OleDbCommand command = connection.CreateCommand();
for (int i = 0; i < count; i++)
{
fieldValue = (item is DataRow) ? fields[i].GetDataRowValue(item) : fields[i].GetValue(item);
if (i == 0)
{
builder.AppendFormat("insert into [{0}$] values(?", sheetName);
}
else
builder.Append(", ?");
parameter = new OleDbParameter(string.Format("param{0}", i), GetOleDBType(fields[i].FieldType));
parameter.Value = fieldValue;
command.Parameters.Add(parameter);
if (i == count - 1)
builder.Append(")");
}
command.CommandText = builder.ToString();
return command;
}
and tada it did insert the rows.
*Tricks: make sure you cast the fields values to its proper type.
Now the nightmare part. the calculations and advanced formatting. this is an excel behavior. if you need to do this you have to create a separate sheet for viewing and other sheets to get the data and make a reference from the view sheet to the other sheets.
the calculations will be on the view sheet. your export function will export to those sheets. And you have to open the file to apply the formatting otherwise it will not affected!!!!
Enjooy the excel loading and exporting.
those tips for formatting Excel.
