本文介绍了批处理作业将获取的数据写入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 ??
推荐答案
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)
等等
这篇关于批处理作业将获取的数据写入MS-Excel?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!