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); } } } }
private void DoSheet(ExcelSheet sheet, ExcelPackage excel, object[] fileargs = null) { if (sheet.SQL != ExcelSQLType.PlainText) { using (DbCommand cmd = conn.SheetConnection.CreateCommand()) { cmd.CommandTimeout = conn.CommandTimeout; cmd.CommandType = sheet.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text; cmd.CommandText = sheet.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); } } using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int currow = 0; object[] args = new object[reader.FieldCount]; reader.GetValues(args); var sh = excel.Workbook.Worksheets.Add(reader.FieldCount > 1 ? reader.GetValue(1).ToString() : "SQLSheet" + (sheetidx++)); Console.WriteLine("Creating sheet '{0}' with given SQL...", sh.Name); var row = sheet.Rows != null?sheet.Rows.FirstOrDefault(r => r.ColumnHeader) : null; var idummy = 0; if (row != null) { DoRow(ref idummy, row, sheet, sh, fileargs, args); } var etcrow = sheet.Rows != null?sheet.Rows.Where(r => !r.ColumnHeader).ToArray() : new ExcelRow[0]; for (int i = 0; i < etcrow.Length; i++) { DoRow(ref currow, etcrow[i], sheet, sh, fileargs, args); } if (sheet.Style != null) { foreach (var style in sheet.Style) { ApplyStyle(sh, style); } } if (sheet.AutoFilter) { sh.Cells[sh.Dimension.Address].AutoFilter = true; } if (sh.Dimension != null) { sh.Cells[sh.Dimension.Address].AutoFitColumns(); } } } } } else { int currow = 0; var sh = excel.Workbook.Worksheets.Add(!string.IsNullOrEmpty(sheet.Name) ? sheet.Name : "OKSheet" + (sheetidx++)); Console.WriteLine("Creating sheet '{0}'...", sh.Name); for (int i = 0; i < (sheet.Rows != null ? sheet.Rows.Count : 0); i++) { DoRow(ref currow, sheet.Rows[i], sheet, sh, fileargs); } if (sheet.Style != null) { foreach (var style in sheet.Style) { ApplyStyle(sh, style); } } if (sheet.AutoFilter) { sh.Cells[sh.Dimension.Address].AutoFilter = true; } if (sh.Dimension != null) { sh.Cells[sh.Dimension.Address].AutoFitColumns(); } } Console.WriteLine("Sheet created!"); }
private void DoSheet(ExcelSheet sheet, ExcelPackage excel, object[] fileargs = null) { if (sheet.SQL != ExcelSQLType.PlainText) { using (DbCommand cmd = conn.SheetConnection.CreateCommand()) { cmd.CommandTimeout = conn.CommandTimeout; cmd.CommandType = sheet.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text; cmd.CommandText = sheet.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); } using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int currow = 0; object[] args = new object[reader.FieldCount]; reader.GetValues(args); var sh = excel.Workbook.Worksheets.Add(reader.FieldCount > 1 ? reader.GetValue(1).ToString() : "SQLSheet" + (sheetidx++)); Console.WriteLine("Creating sheet '{0}' with given SQL...", sh.Name); var row = sheet.Rows != null ? sheet.Rows.FirstOrDefault(r => r.ColumnHeader) : null; var idummy = 0; if (row != null) DoRow(ref idummy, row, sheet, sh, fileargs, args); var etcrow = sheet.Rows != null ? sheet.Rows.Where(r => !r.ColumnHeader).ToArray() : new ExcelRow[0]; for (int i = 0; i < etcrow.Length; i++) DoRow(ref currow, etcrow[i], sheet, sh, fileargs, args); if (sheet.Style != null) foreach (var style in sheet.Style) ApplyStyle(sh, style); if (sheet.AutoFilter) sh.Cells[sh.Dimension.Address].AutoFilter = true; if (sh.Dimension != null) sh.Cells[sh.Dimension.Address].AutoFitColumns(); } } } } else { int currow = 0; var sh = excel.Workbook.Worksheets.Add(!string.IsNullOrEmpty(sheet.Name) ? sheet.Name : "OKSheet" + (sheetidx++)); Console.WriteLine("Creating sheet '{0}'...", sh.Name); for (int i = 0; i < (sheet.Rows != null ? sheet.Rows.Count : 0); i++) DoRow(ref currow, sheet.Rows[i], sheet, sh, fileargs); if (sheet.Style != null) foreach (var style in sheet.Style) ApplyStyle(sh, style); if (sheet.AutoFilter) sh.Cells[sh.Dimension.Address].AutoFilter = true; if (sh.Dimension != null) sh.Cells[sh.Dimension.Address].AutoFitColumns(); } Console.WriteLine("Sheet created!"); }
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); } } }
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; }