private void DoCell(ref int cellnum, ExcelCell cell, ExcelRow row, ExcelSheet sheet, ExcelRange exRow, object[] fileargs = null, object[] sheetargs = null, object[] rowargs = null)
{
//Cell Value
int cnt = cellnum;
Action <string> cellfunc = val =>
{
using (var cl = exRow[exRow.Start.Row, cnt++])
{
//Console.WriteLine("Writing cell {0}...", cl.Start.Address);
if (val != null)
{
double nval;
long lval;
DateTime dval;
bool isFomula = !string.IsNullOrEmpty(val) && val[0] == '=';
string rawval = isFomula ? val.Substring(1) : val;
switch (cell.Out)
{
case ExcelOutType.DateTime:
cl.Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
if (isFomula)
{
cl.Formula = rawval;
}
else if (DateTime.TryParse(rawval, out dval))
{
cl.Value = dval;
}
else
{
cl.Value = rawval;
}
if (cl.Text.EndsWith("00:00:00"))
{
cl.Style.Numberformat.Format = "yyyy-mm-dd";
}
break;
case ExcelOutType.Date:
cl.Style.Numberformat.Format = "yyyy-mm-dd";
if (isFomula)
{
cl.Formula = rawval;
}
else if (DateTime.TryParse(rawval, out dval))
{
cl.Value = dval;
}
else
{
cl.Value = rawval;
}
break;
case ExcelOutType.Integer:
cl.Style.Numberformat.Format = "#,##0";
if (isFomula)
{
cl.Formula = rawval;
}
else if (long.TryParse(rawval, out lval))
{
cl.Value = lval;
}
else
{
cl.Value = rawval;
}
break;
case ExcelOutType.Number:
cl.Style.Numberformat.Format = "#,##0.00";
if (isFomula)
{
cl.Formula = rawval;
}
else if (double.TryParse(rawval, out nval))
{
cl.Value = nval;
}
else
{
cl.Value = rawval;
}
break;
case ExcelOutType.Money:
cl.Style.Numberformat.Format = "￦ #,##0";
if (isFomula)
{
cl.Formula = rawval;
}
else if (long.TryParse(rawval, out lval))
{
cl.Value = lval;
}
else
{
cl.Value = rawval;
}
break;
case ExcelOutType.Normal:
default:
if (isFomula)
{
cl.Formula = rawval;
}
else
{
cl.Value = rawval;
}
break;
}
}
}
};
if (cell.SQL != ExcelSQLType.PlainText)
{
using (DbCommand cmd = conn.CellConnection.CreateCommand())
{
cmd.CommandTimeout = conn.CommandTimeout;
cmd.CommandType = cell.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
cmd.CommandText = cell.Text;
if (fileargs != null)
{
for (int i = 0; i < fileargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + i : "F" + i;
param.Value = fileargs[i];
cmd.Parameters.Add(param);
}
}
if (sheetargs != null)
{
for (int i = 0; i < sheetargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + i : "S" + i;
param.Value = sheetargs[i];
cmd.Parameters.Add(param);
}
}
if (rowargs != null)
{
for (int i = 0; i < rowargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@R" + i : "R" + i;
param.Value = rowargs[i];
cmd.Parameters.Add(param);
}
}
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cellfunc.Invoke(reader.GetValue(0).ToString());
}
}
}
}
else if (cell.Text != null)
{
cellfunc.Invoke(cell.Text);
}
cellnum = cnt;
}
private void DoRow(ref int rownum, ExcelRow row, ExcelSheet sheet, ExcelWorksheet exSheet, object[] fileargs = null, object[] sheetargs = null)
{
//Process Cell
if (row.SQL != ExcelSQLType.PlainText)
{
using (DbCommand cmd = conn.RowConnection.CreateCommand())
{
cmd.CommandTimeout = conn.CommandTimeout;
cmd.CommandType = row.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
cmd.CommandText = row.Text;
if (fileargs != null)
{
for (int i = 0; i < fileargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + i : "F" + i;
param.Value = fileargs[i];
cmd.Parameters.Add(param);
}
}
if (sheetargs != null)
{
for (int i = 0; i < sheetargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + i : "S" + i;
param.Value = sheetargs[i];
cmd.Parameters.Add(param);
}
}
using (DbDataReader reader = cmd.ExecuteReader())
{
if (row.ColumnHeader)
{
int curcell = 1;
using (var rw = exSheet.Cells[++rownum + ":" + rownum])
{
Console.WriteLine("Writing row {0} with Column header...", rw.Address);
var cols = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();
for (int i = 0; i < reader.FieldCount; i++)
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = cols[i];
//Console.WriteLine("Column '{0}' is have a type of '{1}'", cols[i], (reader.GetFieldType(i) ?? typeof(string)).FullName);
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, cols);
}
exSheet.View.FreezePanes(2, 1);
}
}
while (reader.Read())
{
int curcell = 1;
using (var rw = exSheet.Cells[++rownum + ":" + rownum])
{
//Console.WriteLine("Writing row {0} with given SQL...", rw.Address);
object[] args = new object[reader.FieldCount];
reader.GetValues(args);
for (int i = 0; i < reader.FieldCount; i++)
{
if (row.Fetch != null)
{
using (DbCommand subcmd = conn.Connection.CreateCommand())
{
cmd.CommandTimeout = conn.CommandTimeout;
subcmd.CommandType = row.Fetch.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
subcmd.CommandText = row.Fetch.Text;
if (fileargs != null)
{
for (int j = 0; j < fileargs.Length; j++)
{
DbParameter param = subcmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + j : "F" + j;
param.Value = fileargs[j];
subcmd.Parameters.Add(param);
}
}
if (sheetargs != null)
{
for (int j = 0; j < sheetargs.Length; j++)
{
DbParameter param = subcmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + j : "S" + j;
param.Value = sheetargs[j];
subcmd.Parameters.Add(param);
}
}
for (int j = 0; j < args.Length; j++)
{
DbParameter param = subcmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@R" + j : "R" + j;
param.Value = args[j];
subcmd.Parameters.Add(param);
}
using (DbDataReader subreader = subcmd.ExecuteReader())
{
if (row.Fetch.Type == ExcelFetchType.Single)
{
if (!subreader.Read())
{
continue;
}
for (int j = 0; j < subreader.FieldCount; j++)
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = subreader.GetValue(j).ToString();
dc.Out = DetermineOutType(subreader.GetFieldType(j));
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
}
}
else
{
while (subreader.Read())
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = subreader.GetValue(0).ToString();
dc.Out = DetermineOutType(subreader.GetFieldType(0));
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
}
}
}
}
}
else
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = reader.GetValue(i).ToString();
dc.Out = DetermineOutType(reader.GetFieldType(i));
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
}
}
}
}
}
}
}
else
{
int curcell = 1;
using (var rw = exSheet.Cells[++rownum + ":" + rownum])
{
//Console.WriteLine("Writing row {0}...", rw.Start.Row);
for (int i = 0; i < (row.Cells != null ? row.Cells.Count : 0); i++)
{
DoCell(ref curcell, row.Cells[i], row, sheet, rw, fileargs, sheetargs);
}
}
}
}