예제 #1
0
 public static void ExportToExcel(string filePath, DataSet ds)
 {
     object updateLinks = Missing.Value;
     excel.ApplicationClass o = new excel.ApplicationClass();
     try
     {
         excel.Workbook workbook = o.Workbooks.Open(filePath, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks, updateLinks);
         for (int i = 0; i < ds.Tables.Count; i++)
         {
             excel.Worksheet worksheet = (excel.Worksheet) workbook.Worksheets.Add(updateLinks, updateLinks, 1, updateLinks);
             worksheet.Name = ds.Tables[i].TableName;
             string data = "";
             for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
             {
                 for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
                 {
                     data = data + ds.Tables[i].Rows[j][k].ToString();
                     if (k < (ds.Tables[i].Columns.Count - 1))
                     {
                         data = data + "\t";
                     }
                 }
                 data = data + "\n";
             }
             Clipboard.SetDataObject("");
             Clipboard.SetDataObject(data);
             ((excel.Range) worksheet.Cells[1, 1]).Select();
             worksheet.Paste(updateLinks, updateLinks);
             Clipboard.SetDataObject("");
         }
         workbook.Close(excel.XlSaveAction.xlSaveChanges, updateLinks, updateLinks);
         Marshal.ReleaseComObject(workbook);
         workbook = null;
     }
     catch (Exception exception)
     {
         throw new Exception(exception.Message);
     }
     finally
     {
         o.Quit();
         Marshal.ReleaseComObject(o);
         o = null;
         GC.Collect();
     }
 }
예제 #2
0
 public void ExportExcel(string titlename, string filePath, string start, string end, string splitTime, char splitCol, DataSet ds, DataSet ds_com)
 {
     string path = "";
     path = filePath.Substring(0, filePath.LastIndexOf("/")) + "/set.ini";
     if (!Files.ExsitFile(path))
     {
         Files.CreateFolder(filePath.Substring(0, filePath.LastIndexOf("/")));
         Files.CreateFile(path);
     }
     if (ds != null)
     {
         excel.Range range;
         excel.Application application = new excel.ApplicationClass();
         if (application == null)
         {
             throw new Exception("无法创建Excel对象,可能您的电脑未安装Excel");
         }
         excel.Workbook workbook = application.Workbooks.Add(excel.XlWBATemplate.xlWBATWorksheet);
         excel.Worksheet worksheet = (excel.Worksheet) workbook.Worksheets[1];
         object across = Missing.Value;
         int num = 1;
         int length = 1;
         long count = ds.Tables[0].Rows.Count;
         long num4 = 0L;
         if (titlename != "")
         {
             range = worksheet.get_Range(worksheet.Cells[num, 1], worksheet.Cells[num, ds.Tables[0].Columns.Count]);
             range.Merge(across);
             range.Font.Bold = true;
             range.Font.Size = 0x18;
             range.RowHeight = 60;
             range.HorizontalAlignment = 3;
             range.Value2 = titlename;
             num++;
         }
         if (start != "")
         {
             range = worksheet.get_Range(worksheet.Cells[num, 1], worksheet.Cells[num, ds.Tables[0].Columns.Count]);
             range.Merge(across);
             range.Font.Size = 12;
             range.RowHeight = 30;
             range.HorizontalAlignment = 3;
             range.Value2 = start + splitTime + end;
             num++;
         }
         for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
         {
             string[] strArray = ds.Tables[0].Columns[i].ColumnName.Split(new char[] { splitCol });
             if ((strArray.Length > 1) && (strArray.Length > length))
             {
                 length = strArray.Length;
             }
         }
         int[] numArray = new int[length];
         for (int j = 0; j < length; j++)
         {
             numArray[j] = 0;
         }
         for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
         {
             string[] strArray2 = ds.Tables[0].Columns[k].ColumnName.Split(new char[] { splitCol });
             if (strArray2.Length > 1)
             {
                 for (int n = 0; n < strArray2.Length; n++)
                 {
                     int num9 = 1;
                     if ((numArray[n] == k) || (numArray[n] == 0))
                     {
                         for (int num10 = k + 1; num10 < ds.Tables[0].Columns.Count; num10++)
                         {
                             string[] strArray3 = ds.Tables[0].Columns[num10].ColumnName.Split(new char[] { splitCol });
                             if (strArray3.Length > n)
                             {
                                 if (!(strArray2[n] == strArray3[n]))
                                 {
                                     break;
                                 }
                                 num9++;
                             }
                         }
                         numArray[n] = num9 + k;
                         if (num9 == 1)
                         {
                             range = (excel.Range) worksheet.Cells[num + n, k + 1];
                         }
                         else
                         {
                             range = worksheet.get_Range(worksheet.Cells[num + n, k + 1], worksheet.Cells[num + n, k + num9]);
                             range.Merge(across);
                         }
                         this.SetStyle(strArray2[n], ref range);
                     }
                 }
                 for (int num11 = 0; num11 < length; num11++)
                 {
                     numArray[num11] = 0;
                 }
             }
             else
             {
                 range = worksheet.get_Range(worksheet.Cells[num, k + 1], worksheet.Cells[(num + length) - 1, k + 1]);
                 range.Merge(across);
                 this.SetStyle(ds.Tables[0].Columns[k].ColumnName, ref range);
             }
         }
         num += length;
         for (int m = 0; m < ds.Tables[0].Rows.Count; m++)
         {
             for (int num13 = 0; num13 < ds.Tables[0].Columns.Count; num13++)
             {
                 worksheet.Cells[m + num, num13 + 1] = "'" + ds.Tables[0].Rows[m][num13];
                 range = (excel.Range) worksheet.Cells[m + num, num13 + 1];
                 this.SetRowStyle(filePath, ds.Tables[0].Rows[m][num13].ToString(), ref range, ds, ds_com, m, num13);
             }
             num4 += 1L;
             float single1 = ((float) (100L * num4)) / ((float) count);
         }
         object filename = new object();
         filename = filePath;
         workbook.SaveAs(filename, across, across, across, across, across, excel.XlSaveAsAccessMode.xlShared, across, across, across, across, across);
         workbook.Close(false, across, across);
         application.Quit();
         GC.Collect();
     }
 }