home *** CD-ROM | disk | FTP | other *** search
/ business-86-101-185-173.business.broadband.hu / business-86-101-185-173.business.broadband.hu.zip / business-86-101-185-173.business.broadband.hu / scs.zip / ExportData.cs < prev    next >
Text File  |  2008-10-06  |  21KB  |  485 lines
  1. ∩╗┐using Microsoft.VisualBasic;
  2. using System.IO;
  3. using BaseClasses.Data;
  4. using CarlosAg.ExcelXmlWriter;
  5. using System;
  6. using System.Collections;
  7.  
  8. // The ExportData class exports to Excel and sends the XLS file to the response stream.
  9. namespace Warehouse
  10. {
  11.  public class ExportData
  12.  {
  13.     #region "Properties"
  14.     private BaseTable _tbl;
  15.     private BaseTable DBTable
  16.     {
  17.             get 
  18.             {
  19.                 return this._tbl;
  20.             }
  21.             set
  22.             {
  23.                 this._tbl = value;
  24.             }
  25.     }
  26.  
  27.     WhereClause _wc;
  28.     private WhereClause SelectWhereClause 
  29.     {
  30.         get
  31.         {
  32.             return this._wc;
  33.         }
  34.         set
  35.         {
  36.             this._wc = value;
  37.         }
  38.     }
  39.         
  40.     OrderBy _orderby;
  41.     private OrderBy SelectOrderBy
  42.     {
  43.         get
  44.         {
  45.             return this._orderby;
  46.         }
  47.         set
  48.         {
  49.             this._orderby = value;
  50.         }
  51.     }
  52.  
  53.     BaseColumn[] _columns;
  54.     private BaseColumn[] DisplayColumns
  55.     {
  56.         get
  57.         {
  58.             return _columns;
  59.         }
  60.             set
  61.             {
  62.                 this._columns = value;
  63.             }
  64.     }
  65.     #endregion
  66.  
  67.     #region "Constructor"
  68.  
  69.     public ExportData(BaseTable tbl, WhereClause wc, OrderBy orderBy, BaseColumn[] columns)
  70.     {
  71.         this.DBTable = tbl;
  72.         this.SelectWhereClause = wc;
  73.         this.SelectOrderBy = orderBy;
  74.         this.DisplayColumns = columns;
  75.     }
  76.     public ExportData(BaseTable tbl, WhereClause wc, OrderBy orderBy)
  77.     {
  78.            this.DBTable = tbl;
  79.            this.SelectWhereClause = wc;
  80.            this.SelectOrderBy = orderBy;
  81.     }
  82.     #endregion
  83.  
  84.     #region "Public Methods"
  85.  
  86.     public void ExportToCSV(System.Web.HttpResponse response)
  87.     {
  88.             bool done = false;
  89.             int pageIndex = 0;
  90.             int pageSize = 100;
  91.             string val;
  92.             bool skip;
  93.             bool IsString;
  94.         
  95.             if (!(response == null))
  96.             {
  97.                 string fileName = (DBTable.TableDefinition.Name + ".csv");
  98.                 response.ClearHeaders();
  99.                 response.Clear();
  100.                 response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
  101.                 response.Cache.SetMaxAge(new TimeSpan(0));
  102.                 response.Cache.SetExpires(new DateTime(0));
  103.                 response.Cache.SetNoServerCaching();
  104.                 response.ContentType = "text/plain";
  105.                 response.AppendHeader("Content-Disposition", ("attachment; filename=\"" + (fileName + "\"")));
  106.                 
  107.         StreamWriter writer = new StreamWriter(response.OutputStream, System.Text.Encoding.UTF8); 
  108.         //  First write out the Column Headers
  109.         foreach (BaseColumn col in DisplayColumns)
  110.         {
  111.             if (!(col == null))
  112.             {
  113.                 if (((col.ColumnType != BaseColumn.ColumnTypes.Binary) && (col.ColumnType != BaseColumn.ColumnTypes.Image)))
  114.                 {
  115.                     writer.Write(("\"" + (col.Name.Replace("\"", "\"\"") + "\",")));
  116.                 }
  117.                 }
  118.         }
  119.         writer.WriteLine();
  120.         int totalRowsReturned = 0;
  121.         //  Read 100 records at a time and write out the CSV file.
  122.         while (!done)
  123.         {
  124.             ArrayList recList = null;
  125.             if (SelectWhereClause.RunQuery)
  126.             {
  127.                 recList = DBTable.GetRecordList(SelectWhereClause.GetFilter(), SelectOrderBy, pageIndex, pageSize, ref totalRowsReturned);
  128.                 totalRowsReturned = recList.Count;
  129.             }
  130.             if ((totalRowsReturned <= 0))
  131.             {
  132.                     done = true;
  133.             }
  134.             else 
  135.             {
  136.                     foreach (BaseRecord rec in recList)
  137.                     {
  138.                             foreach (BaseColumn col in DisplayColumns)
  139.                             {
  140.                                 if (col != null)
  141.                                 {
  142.                                         skip = false;
  143.                                         val = "";
  144.                                         IsString = true;
  145.                                         if (col.TableDefinition.IsExpandableNonCompositeForeignKey(col))
  146.                                         {
  147.                                             //  Foreign Key column, so we will use DFKA and String type.
  148.                                             val = rec.Format(col);
  149.                                         }
  150.                                     else
  151.                                     {
  152.                             switch (col.ColumnType)
  153.                             {
  154.                                 case BaseColumn.ColumnTypes.Binary:
  155.                                 case BaseColumn.ColumnTypes.Image:
  156.                                                     //  Skip - do nothing for these columns
  157.                                                     skip = true;
  158.                                                     break;
  159.                                 case BaseColumn.ColumnTypes.Currency:
  160.                                 case BaseColumn.ColumnTypes.Number:
  161.                                 case BaseColumn.ColumnTypes.Percentage:
  162.                                                     IsString = false;
  163.                                                     val = rec.Format(col);
  164.                                                     break;
  165.                                                 default:
  166.                                                     val = rec.Format(col);
  167.                                                     break;
  168.                                         }
  169.                                     }
  170.                                     if ((val == null))
  171.                                     {
  172.                                         val = "";
  173.                                     }
  174.                                     if (!skip) 
  175.                                     {
  176.                                         if ((IsString) || (val.Contains(","))) 
  177.                                         {
  178.                                                 writer.Write(("\"" + (val.Replace("\"", "\"\"") + "\",")));
  179.                             }
  180.                             else
  181.                             {
  182.                                                 writer.Write((val + ","));
  183.                                         }
  184.                                     }
  185.                             }
  186.                         }
  187.             writer.WriteLine();
  188.         }
  189.             pageIndex++;
  190.             //  If we already are below the pageSize, then we are done.
  191.             if ((totalRowsReturned < pageSize))
  192.             {
  193.                     done = true;
  194.             }
  195.        }
  196.     }
  197.     writer.Flush();
  198.     response.End();      
  199.     }
  200. }
  201.  
  202.  
  203.       ArrayList ColumnList = new ArrayList();
  204.        public void AddColumn(ExcelColumn col) 
  205.        {
  206.            this.ColumnList.Add(col);
  207.        }
  208.  
  209.     CarlosAg.ExcelXmlWriter.DataType excelDataType;
  210.     public void ExportToExcel(System.Web.HttpResponse response)
  211.     {
  212.         bool done = false;
  213.         string val;
  214.         bool skip;
  215.         bool skipHeader;
  216.  
  217.             if (!(response == null))
  218.             {                
  219.                 CarlosAg.ExcelXmlWriter.Workbook excelBook = new CarlosAg.ExcelXmlWriter.Workbook();
  220.                 excelBook.Properties.Title = DBTable.TableDefinition.Name;
  221.                 excelBook.Properties.Created = DateTime.Now;
  222.                 excelBook.Properties.Author = "";
  223.  
  224.                 CarlosAg.ExcelXmlWriter.Worksheet excelSheet = excelBook.Worksheets.Add("Sheet1");
  225.                 excelSheet.Options.SplitHorizontal = 1;
  226.                 excelSheet.Options.FreezePanes = true;
  227.                 excelSheet.Options.TopRowBottomPane = 1;
  228.  
  229.                 CarlosAg.ExcelXmlWriter.WorksheetRow excelRow = excelSheet.Table.Rows.Add();
  230.                 
  231.                 CarlosAg.ExcelXmlWriter.WorksheetStyle excelHeaderStyle = excelBook.Styles.Add("HeaderRowStyle");
  232.                 excelHeaderStyle.Font.Bold = true;
  233.                 excelHeaderStyle.Font.FontName = "Verdana";
  234.                 excelHeaderStyle.Font.Size = 8;
  235.                 excelHeaderStyle.Font.Color = "#FFFFFF";
  236.                 excelHeaderStyle.Interior.Color = "#000000";
  237.                 excelHeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid;
  238.  
  239.                 CarlosAg.ExcelXmlWriter.WorksheetStyle excelCurrencyStyle = excelBook.Styles.Add("CurrencyRowStyle");
  240.                 excelCurrencyStyle.Font.FontName = "Verdana";
  241.                 excelCurrencyStyle.Font.Size = 8;
  242.                 excelCurrencyStyle.Alignment.Vertical = StyleVerticalAlignment.Bottom;
  243.                 excelCurrencyStyle.Alignment.Horizontal = StyleHorizontalAlignment.Right;
  244.             
  245.                 int width = 0;
  246.  
  247.               //  First write out the Column Headers
  248.                 foreach (ExcelColumn elem in ColumnList){
  249.                     ExcelColumn exCol = ((ExcelColumn)(elem));
  250.                     BaseColumn col = exCol.DisplayColumn;
  251.                     if (!(col == null)) {
  252.                         skipHeader = false;
  253.                         if (col.TableDefinition.IsExpandableNonCompositeForeignKey(col)) {
  254.                             // Set width if field is a foreign key field
  255.                             width = 100;
  256.                         }
  257.                         else {
  258.                             switch (col.ColumnType) {
  259.                             case BaseColumn.ColumnTypes.Binary:
  260.                             case BaseColumn.ColumnTypes.Image:
  261.                                 //  Skip - do nothing for these columns
  262.                                 skipHeader = true;
  263.                                 break;
  264.                             case BaseColumn.ColumnTypes.Currency:
  265.                             case BaseColumn.ColumnTypes.Number:
  266.                             case BaseColumn.ColumnTypes.Percentage:
  267.                                 width = 60;
  268.                                 break;
  269.                             case BaseColumn.ColumnTypes.String:
  270.                             case BaseColumn.ColumnTypes.Very_Large_String:
  271.                                 width = 110;
  272.                                 break;
  273.                             default:
  274.                                 width = 50;
  275.                                 break;
  276.                         }
  277.                     }
  278.                     if (!skipHeader) {
  279.                         // excelHeaderStyle.NumberFormat = exCol.DisplayFormat
  280.                         excelRow.Cells.Add(new WorksheetCell(col.Name, "HeaderRowStyle"));
  281.                         WorksheetColumn worksheetColumn = excelSheet.Table.Columns.Add(width);
  282.                     }
  283.                 }
  284.             }
  285.                 // Read 100 records at a time and write out the Excel file.
  286.                 int pageIndex = 0;
  287.                 int pageSize = 100;
  288.                 int totalRowsReturned = 0;
  289.                 int rowCounter = 0;
  290.                 decimal deciNumber;
  291.  
  292.                 while (!done)
  293.                 {
  294.                     ArrayList recList = null;
  295.                     if (SelectWhereClause.RunQuery)
  296.                     {
  297.                         recList = DBTable.GetRecordList(SelectWhereClause.GetFilter(), SelectOrderBy, pageIndex, pageSize, ref totalRowsReturned);
  298.                         totalRowsReturned = recList.Count;
  299.                     }
  300.                     if ((totalRowsReturned <= 0))
  301.                     {
  302.                         done = true;
  303.                     }
  304.                     else
  305.                     {
  306.                         foreach (BaseRecord rec in recList)
  307.                         {
  308.                             excelRow = excelSheet.Table.Rows.Add();
  309.                             int columnCounter = 0;
  310.                             foreach (ExcelColumn elem in ColumnList)
  311.                             {
  312.                                 ExcelColumn exCol = ((ExcelColumn)(elem));
  313.                                 BaseColumn col = exCol.DisplayColumn;
  314.                                 if (col != null)
  315.                                 {
  316.                                     skip = false;
  317.                                     val = "";
  318.                                     if (col.TableDefinition.IsExpandableNonCompositeForeignKey(col))
  319.                                     {
  320.                                         //  Foreign Key column, so we will use DFKA and String type.
  321.                                         excelDataType = DataType.String;
  322.                                         val = rec.Format(col);
  323.                                     }
  324.                                     else
  325.                                     {
  326.                                         switch (col.ColumnType) 
  327.                                         {
  328.                                             case BaseColumn.ColumnTypes.Binary:
  329.                                             case BaseColumn.ColumnTypes.Image:
  330.                                                 //  Skip - do nothing for these columns
  331.                                                 skip = true;
  332.                                                 break;
  333.                                             case BaseColumn.ColumnTypes.Number:
  334.                                             case BaseColumn.ColumnTypes.Percentage:
  335.                                                 excelDataType = DataType.Number;
  336.                                                 ColumnValue numVal = rec.GetValue(col);
  337.                                                 //If the value of the column to be exported is nothing, add an empty cell to the Excel file
  338.                                                 if (numVal.IsNull)
  339.                                                 {
  340.                                                     excelRow.Cells.Add(new WorksheetCell());
  341.                                                     skip = true;
  342.                                                 }
  343.                                                 else
  344.                                                 {
  345.                                                     deciNumber = numVal.ToDecimal();
  346.                                                     val = deciNumber.ToString(System.Globalization.CultureInfo.InvariantCulture);
  347.                                                 }
  348.                                                 break;
  349.                                             case BaseColumn.ColumnTypes.Currency:
  350.                                                 excelDataType = DataType.Number;
  351.                                                 ColumnValue curVal = rec.GetValue(col);
  352.                                                 //If the value of the column to be exported is nothing, add an empty cell to the Excel file
  353.                                                 if (curVal.IsNull)
  354.                                                 {
  355.                                                     excelRow.Cells.Add(new WorksheetCell());
  356.                                                     skip = true;
  357.                                                 }
  358.                                                 else
  359.                                                 {
  360.                                                     deciNumber = curVal.ToDecimal();
  361.                                                     val = deciNumber.ToString(System.Globalization.CultureInfo.InvariantCulture);                                    
  362.                                                 }
  363.                                                 break;
  364.                                             case BaseColumn.ColumnTypes.Date:
  365.                                                 excelDataType = DataType.DateTime;
  366.                                                 ColumnValue dateVal = rec.GetValue(col);
  367.                                                 if (dateVal.IsNull) 
  368.                                                 {
  369.                                                     excelRow.Cells.Add(new WorksheetCell());
  370.                                                     skip = true;
  371.                                                 }
  372.                                                 else 
  373.                                                 {  // Specify the default Excel format for the date field 
  374.                                                     val = rec.Format(col, "s");
  375.                                                     val += ".000";
  376.                                                 }
  377.                                                 break;
  378.                                             case BaseColumn.ColumnTypes.Very_Large_String:
  379.                                                 excelDataType = DataType.String;
  380.                                                 val = rec.GetValue(col).ToString();
  381.                                                 break;
  382.                                             
  383.                                             case BaseColumn.ColumnTypes.Boolean:
  384.                                                 excelDataType = DataType.String;
  385.                                                 val = rec.Format(col);
  386.                                                 break;
  387.  
  388.                                             default:
  389.                                                 excelDataType = DataType.String;
  390.                                                 val = rec.Format(col);
  391.                                                 break;
  392.                                         }
  393.                                     }
  394.                                     // Define a unique column style for each column in the table to be exported
  395.                                     if ((rowCounter == 0)) 
  396.                                     {
  397.                                         CarlosAg.ExcelXmlWriter.WorksheetStyle excelColumnStyle = excelBook.Styles.Add((exCol.DisplayColumn.ToString() + columnCounter));
  398.                                         excelColumnStyle.Font.FontName = "Verdana";
  399.                                         excelColumnStyle.Font.Size = 8;
  400.                                         if (!exCol.DisplayFormat.Equals("Default")) 
  401.                                         {
  402.                                             excelColumnStyle.NumberFormat = exCol.DisplayFormat;
  403.                                         }
  404.                                     }
  405.  
  406.                                     if (!skip) 
  407.                                     {
  408.                                         excelRow.Cells.Add(new WorksheetCell(val, excelDataType, (exCol.DisplayColumn.ToString() + columnCounter)));
  409.                                     
  410.                                     }
  411.                                 }
  412.                                 columnCounter++;
  413.                             }
  414.                             rowCounter++;
  415.                         }
  416.                         pageIndex++;
  417.                         // If we already are below the pageSize, then we are done.
  418.                         if ((totalRowsReturned < pageSize))
  419.                         {
  420.                            done = true;
  421.                         }
  422.                     }
  423.                 }
  424.                 string fileName;
  425.                 if (DBTable.TableDefinition.Name != null)
  426.                 {
  427.                     fileName = (DBTable.TableDefinition.Name + ".xls");
  428.                 }
  429.                 else 
  430.                 {
  431.                     fileName = "Untitled.xls";
  432.                 }
  433.                 response.ClearHeaders();
  434.                 response.Clear();
  435.                 response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
  436.                 response.Cache.SetMaxAge(new TimeSpan(0));
  437.                 response.Cache.SetExpires(new DateTime(0));
  438.                 response.Cache.SetNoServerCaching();
  439.                 response.ContentType = "application/vnd.ms-excel";
  440.                 response.AppendHeader("Content-Disposition", ("attachment; filename=\"" + (fileName + "\"")));
  441.                 excelBook.Save(response.OutputStream);
  442.                 response.End();
  443.             }
  444.         }
  445.     #endregion
  446.  }
  447.  
  448.   public class ExcelColumn 
  449.   {
  450.     #region "Properties"   
  451.       private BaseColumn _column;
  452.       private string _format;
  453.       #endregion
  454.       
  455.     #region "Constructor"
  456.       public ExcelColumn(BaseColumn col, string format)
  457.       {
  458.         DisplayColumn = col;
  459.         DisplayFormat = format;
  460.       }
  461.        #endregion
  462.  
  463.     #region "Public Methods"
  464.        public BaseColumn DisplayColumn 
  465.        {
  466.           get {
  467.               return _column;
  468.           }
  469.           set {
  470.               this._column = value;
  471.           }
  472.        }
  473.     
  474.        public string DisplayFormat 
  475.        {
  476.             get {
  477.                 return _format;
  478.             }
  479.             set {
  480.                 this._format = value;
  481.             }
  482.         }
  483.       #endregion
  484.   }
  485. }