public bool SetData(DataSet ds, string TabName)
{
bool result = false;
int i = 0, j = 0;
bool HeaderGenerated = false;
WorksheetRow row = null;
WorksheetColumn col = null;
WorksheetStyle style = this.Styles.Add("Default");
style = this.Styles.Add("_HeaderStyle");
style.Font.Bold = true;
style = this.Styles.Add("_DateTime");
style.NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
style = this.Styles.Add("_Integer");
style.NumberFormat = "0";
ExcelWorksheet sheet = this.WorkSheets.Add(TabName);
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
for (j = 0; j <= dt.Rows.Count - 1; j++)
{
row = sheet.Table.Rows.Add();
if (!HeaderGenerated)
{
for (i = 0; i < dt.Columns.Count - 1; i++)
{
col = sheet.Table.Columns.Add();
col.AutoFitWidth = true;
col.Width = 99.75;
switch (dt.Rows[j][i].GetType().Name)
{
case "DateTime":
col.StyleName = "_DateTime";
break;
case "Int16":
col.StyleName = "_Integer";
break;
}
row.Cells.Add(dt.Rows[j][i].ToString(), "_HeaderStyle");
}
HeaderGenerated = true;
}
else
{
WorksheetCell cell = null;
for (i = 0; i < dt.Columns.Count - 1; i++)
{
switch (dt.Rows[j][i].GetType().Name)
{
case "DateTime":
cell = new WorksheetCell();
if (dt.Rows[j][i] != System.DBNull.Value)
cell = row.Cells.Add(((DateTime)dt.Rows[j][i]).Date.ToString("yyyy-MM-ddTHH:mm:ss.fff"), CellDataType.DateTime);
else
cell = row.Cells.Add("", CellDataType.String);
break;
case "Int16":
cell = new WorksheetCell();
if (dt.Rows[j][i] != System.DBNull.Value)
cell = row.Cells.Add(dt.Rows[j][i].ToString(), CellDataType.Number);
else
cell = row.Cells.Add("", CellDataType.String);
break;
default:
row.Cells.Add(dt.Rows[j][i].ToString());
break;
}
}
}
}
result = true;
}
else
{
result = false;
}
return result;
}
public bool SetData(SqlDataReader reader, string TabName)
{
bool result = false;
int i = 0;
bool HeaderGenerated = false;
WorksheetRow row = null;
WorksheetColumn col = null;
WorksheetStyle style = this.Styles.Add("Default");
style = this.Styles.Add("_HeaderStyle");
style.Font.Bold = true;
style = this.Styles.Add("_DateTime");
style.NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
style = this.Styles.Add("_Integer");
style.NumberFormat = "0";
ExcelWorksheet sheet = this.WorkSheets.Add(TabName);
if (reader.HasRows)
{
while (reader.Read())
{
row = sheet.Table.Rows.Add();
if (!HeaderGenerated)
{
for (i = 0; i < reader.FieldCount - 1; i++)
{
col = sheet.Table.Columns.Add();
col.AutoFitWidth = true;
col.Width = 99.75;
switch (reader.GetFieldType(i).Name)
{
case "DateTime":
col.StyleName = "_DateTime";
break;
case "Int16":
col.StyleName = "_Integer";
break;
}
row.Cells.Add(reader.GetName(i), "_HeaderStyle");
}
HeaderGenerated = true;
}
else
{
WorksheetCell cell = null;
for (i = 0; i < reader.FieldCount - 1; i++)
{
switch (reader.GetFieldType(i).Name)
{
case "DateTime":
cell = new WorksheetCell();
if (reader[i] != System.DBNull.Value)
cell = row.Cells.Add(reader.GetDateTime(i).ToString("yyyy-MM-ddTHH:mm:ss.fff"), CellDataType.DateTime);
else
cell = row.Cells.Add("", CellDataType.String);
break;
case "Int16":
cell = new WorksheetCell();
if (reader[i] != System.DBNull.Value)
cell = row.Cells.Add(reader.GetInt16(i).ToString(), CellDataType.Number);
else
cell = row.Cells.Add("", CellDataType.String);
break;
default:
row.Cells.Add(reader[i].ToString());
break;
}
}
}
}
result = true;
}
else
{
result = false;
}
return result;
}
public WorksheetCell Add(string Value, string StyleName, string NamedCell)
{
WorksheetCell cell = new WorksheetCell();
cell.Data.Value = Value;
cell.StyleName = StyleName;
cell.Data.NamedCell = NamedCell;
_Cells.Add(cell);
return cell;
}
public WorksheetCell Add(string Value, string StyleName, CellDataType DataType)
{
WorksheetCell cell = new WorksheetCell();
cell.Data.Value = Value;
cell.StyleName = StyleName;
cell.Data.Type = DataType;
_Cells.Add(cell);
return cell;
}
public WorksheetCell Add(string Value)
{
WorksheetCell cell = new WorksheetCell();
cell.Data.Value = Value;
_Cells.Add(cell);
return cell;
}
public WorksheetCell Add(WorksheetCell Cell)
{
_Cells.Add(Cell);
return Cell;
}
public WorksheetCell Add()
{
WorksheetCell cell = new WorksheetCell();
_Cells.Add(cell);
return cell;
}