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

05-28 21:49
查看更多