批处理作业将获取的数据写入MS-Excel?
问题描述：
Im Developing a ASP.NET Website and i need a batch job which fetches the data from database to MS-Excel ? How to write the fetched data to MS-Excel ?
I am using webservice to insert the data to database to retrieve i need to use a batch file ??
答
有几个选项.
在这种情况下，我应该使用:
-运行作业的Windows服务
-包含要写入Excel文件的数据的DataTable
-CarlosAG XMLWriter创建Excel 1997-2003 xls文件(请参见此处)
您可以通过CarlosAG ExcelXMLWriter找到很多创建Excel文件的示例.
您可以使用数据表的datacolumn.column名称创建标题，并使用表格的数据行将数据写入其中.
我的例子:
There are several options.
In this case I should use:
- A Windows service that runs the job
- DataTable with Data to write to an excel file
- CarlosAG XMLWriter to create Excel 1997-2003 xls files (see here)
You can find a lot of examples to create a excel file via the CarlosAG ExcelXMLWriter.
You can use the datacolumn.columnnames of the datatable to create headers and use the datarows of the tabel to write the data into.
My Example:
public class CreateExcel2003orCSV
{
public Int32 DataToExcel(DataTable dt, String FileName, String ReportCaption, String sheetName)
{
try
{
if (dt.Rows.Count > 0)
{
Int32 RowCount = dt.Rows.Count;
SesameCommon.WindowsLog.LogErrors.LogInformation("Setting " + RowCount.ToString("#,0") + " rows from DATATABLE object to EXCEL object for file " + FileName, "SesameReports");
SesameCommon.ServiceMessage.SendServiceMessage("Setting " + RowCount.ToString("#,0") + " rows from DATATABLE object to EXCEL object for file " + FileName);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-IE");
FileInfo info = new FileInfo(FileName);
if (info.Exists)
{
File.Delete(FileName);
}
Workbook book = new Workbook();
book.Worksheets.Clear();
Int32 AbsoluteRowCount = 0; // to match dt.Rows.Count value
Int32 PagNr = 1; // in case of multiple sheets in the workbook (65,000 lines per sheet)
Int32 _currentLine = 0;
String OrgFileName = sheetName;
while (!AbsoluteRowCount.Equals(RowCount))
{
Worksheet sheet = null;
if (PagNr.Equals(1))
{
if (sheetName.Length < 13)
sheetName = OrgFileName;
else
sheetName = OrgFileName.Substring(0, 12) + "_" + PagNr.ToString();
}
else
{
// When too many letters for the filename Adjust the filename
if (sheetName.Length < 13)
sheetName = OrgFileName + "_" + PagNr.ToString();
else
sheetName = OrgFileName.Substring(0, 12) + "_" + PagNr.ToString();
}
sheet = book.Worksheets.Add(sheetName);
sheet.Table.StyleID = "tbl";
sheet.Table.DefaultColumnWidth = 85;
sheet.Table.DefaultRowHeight = 15;
sheet.Options.FitToPage = true;
// Default information about the shown data
WorksheetRow captionRow = sheet.Table.Rows.Add();
captionRow.Height = 18;
WorksheetCell captionCell = captionRow.Cells.Add(ReportCaption + " [rows: " + RowCount.ToString("#,0") + "]", DataType.String, "tblHeaderWhite");
captionCell.MergeAcross = dt.Columns.Count - 1;
WorksheetRow headerRow = sheet.Table.Rows.Add();
headerRow.Height = 18;
String styleNameRow = String.Empty;
String dcCaption = String.Empty;
//Add ColumnNames
foreach (DataColumn dc in dt.Columns)
{
WorksheetColumn column = new WorksheetColumn();
sheet.Table.Columns.Add(column);
headerRow.Cells.Add(dc.Caption, DataType.String, "tblHeader");
}
String gridStylClass = String.Empty;
// Add Data
for (Int32 i = AbsoluteRowCount; i < RowCount; i++)
{
AbsoluteRowCount++;
_currentLine++;
styleNameRow = GenerateWorkSheetRow(dt, sheet, styleNameRow, i);
foreach (WorksheetColumn column in sheet.Table.Columns)
{
column.AutoFitWidth = true;
}
if (_currentLine.Equals(65000))
{
PagNr++;
_currentLine = 0;
book.Save(FileName);
break;
}
}
}
Sesame.styleWorkBook(book, true);
book.Save(FileName);
book = null;
}
}
catch (Exception err)
{
return -1;
}
return 0;
}
private static String GenerateWorkSheetRow(DataTable dt, Worksheet sheet, String styleNameRow, Int32 i)
{
WorksheetRow workRow = new WorksheetRow();
if (styleNameRow.Equals("tblRow"))
styleNameRow = "tblRowAlt";
else
styleNameRow = "tblRow";
String SetBold = String.Empty;
foreach (DataColumn dc in dt.Columns)
{
if (dt.Rows[i][dc].Equals(DBNull.Value))
{
if (dc.DataType.Name.Equals("Decimal"))
{
Decimal dec = 0;
workRow.Cells.Add(dec.ToString("#,0.00").Trim(), DataType.Number, styleNameRow + "Number" + SetBold);
}
else if (dc.DataType.Name.Equals("Int32") || dc.DataType.Name.Equals("Int16") || dc.DataType.Name.Equals("Int64"))
{
int getal = 0;
workRow.Cells.Add(getal.ToString("#,0").Trim(), DataType.String, styleNameRow + "NumberVer" + SetBold);
}
else if (dc.DataType.Name.Equals("DateTime"))
{
workRow.Cells.Add(String.Empty, DataType.String, styleNameRow + SetBold);
}
else
workRow.Cells.Add(String.Empty, DataType.String, styleNameRow + SetBold);
}
else if (dc.DataType.Equals(typeof(String))) // Type.GetType("System.String")))
{
workRow.Cells.Add(dt.Rows[i][dc].ToString(), DataType.String, styleNameRow + SetBold);
}
else if (dc.DataType.Equals(typeof(DateTime))) // Type.GetType("System.DateTime")))
{
DateTime _date = (DateTime)dt.Rows[i][dc];
workRow.Cells.Add(_date.ToString("dd/MMM/yyyy"), DataType.String, styleNameRow + "Date" + SetBold);
}
else if (dc.DataType.Equals(typeof(Decimal))) // Type.GetType("System.Decimal")))
{
Decimal dec = (Decimal)dt.Rows[i][dc];
workRow.Cells.Add(dec.ToString("#,0.00").Trim(), DataType.Number, styleNameRow + "Number" + SetBold);
}
else if (dc.DataType.Equals(typeof(Double))) // Type.GetType("System.Double")))
{
Double dob = (Double)dt.Rows[i][dc];
workRow.Cells.Add(dob.ToString("#,0.00").Trim(), DataType.Number, styleNameRow + "Number" + SetBold);
}
else if (dc.DataType.Equals(typeof(Int16))) // Type.GetType("System.Int16")))
{
Int16 getal = (Int16)dt.Rows[i][dc];
workRow.Cells.Add(getal.ToString("#,0").Trim(), DataType.String, styleNameRow + "NumberVer" + SetBold);
}
else if (dc.DataType.Equals(typeof(Int32))) // Type.GetType("System.Int32")))
{
Int32 getal = (Int32)dt.Rows[i][dc];
workRow.Cells.Add(getal.ToString("#,0").Trim(), DataType.Number, styleNameRow + "NumberVer" + SetBold);
}
else if (dc.DataType.Equals(typeof(Int64))) // Type.GetType("System.Int64")))
{
Int64 getal = (Int64)dt.Rows[i][dc];
workRow.Cells.Add(getal.ToString("#,0").Trim(), DataType.Number, styleNameRow + "NumberVer" + SetBold);
}
else if (dc.DataType.Equals(typeof(Boolean))) // Type.GetType("System.Boolean")))
{
Boolean logic = (Boolean)dt.Rows[i][dc];
workRow.Cells.Add(logic.ToString().Trim(), DataType.String, styleNameRow + SetBold);
}
else
workRow.Cells.Add(dt.Rows[i][dc].ToString(), DataType.String, styleNameRow + SetBold);
}
workRow.AutoFitHeight = true;
sheet.Table.Rows.Add(workRow);
return styleNameRow;
}
public Int32 DataToCSV(DataTable dt, String FileName, String ReportCaption)
{
try
{
if (dt.Rows.Count > 0)
{
Int32 RowCount = dt.Rows.Count;
FileInfo info = new FileInfo(FileName);
if (info.Exists)
{
File.Delete(FileName);
}
StreamWriter writer = new StreamWriter(FileName, true);
writer.WriteLine(ReportCaption);
//Add Header Row
StringBuilder _dataRow = new StringBuilder();
foreach (DataColumn column in dt.Columns)
{
if (_dataRow.Length > 0)
_dataRow.Append(",");
_dataRow.Append(column.Caption);
}
writer.WriteLine(_dataRow.ToString());
// Add data
foreach (DataRow row in dt.Rows) // Loop over the rows.
{
_dataRow = new StringBuilder();
foreach (var item in row.ItemArray) // Loop through items.
{
if (_dataRow.Length > 0)
_dataRow.Append(",");
if (item == DBNull.Value)
_dataRow.Append(" ");
else
_dataRow.Append(item.ToString().Replace(",", String.Empty)); // remove any , signs in a field!
}
writer.WriteLine(_dataRow.ToString());
}
writer.Flush();
writer.Close();
}
}
catch (Exception err)
{
}
return 0;
}
}
我使用样式模板进行着色，这是一个c#类.小例子:
I use a style template for colouring which is a c# class. Small example:
private static String dateFormate = "dd/MMM/yyyy";
private static String numberFormate = "#,##0.00;[Red]-#,##0.00";
private static String numberFormateVer = "#,##0;[Red]-#,##0";
private static int fontSize = 7;
private static String fontName = "Verdana";
private static int borderSize = 2;
private static String borderColor = "Black";
/// <summary>
/// styleMyGridView: add basic styling to a GridView component
/// </summary>
/// <param name="gridView">Name of the GridView to add styling to</param>
/// <summary>
/// styleWorkBook: Add styling to an excel workbook
/// </summary>
/// <param name="book">Excel workboox</param>
/// <param name="border">Border around each cell</param>
public static void styleWorkBook(Workbook book, Boolean border)
{
WorksheetStyle wss = new WorksheetStyle("tbl");
wss.Font.Color = "Black";
wss.Font.FontName = fontName;
wss.Font.Size = fontSize;
wss.Interior.Color = "White";
book.Styles.Add(wss);
wss = new WorksheetStyle("tblHeaderWhite");
wss.Interior.Color = "White";
wss.Font.FontName = fontName;
wss.Font.Size = fontSize;
wss.Font.Bold = true;
book.Styles.Add(wss);
wss = new WorksheetStyle("tblHeader");
wss.Font.FontName = fontName;
wss.Font.Size = fontSize;
wss.Interior.Color = "#8AC8FA"; // "#A8ACAF";
wss.Interior.Pattern = StyleInteriorPattern.Solid;
wss.Font.Bold = true;
wss.Alignment.Vertical = StyleVerticalAlignment.Bottom;
wss.Alignment.Horizontal = StyleHorizontalAlignment.Left;
if (border)
{
wss.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, borderSize, borderColor);
wss.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, borderSize, borderColor);
wss.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, borderSize, borderColor);
wss.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, borderSize, borderColor);
}
book.Styles.Add(wss)
等等