对导出Excel的自动列宽的处理

在系统中往往有导出Excel文档，其中的列宽不能自适应给用户带来不方便需要手动进行设置，因此做了个列宽自适应的方法，作为日志记录

注意：需要引用 Microsoft.Office.Interop.Excel

View Code

  1 public  class ExcelMake
  2 {
  3       public static void reportByExcel()
  4         { 
  5             string excelStr = "";
  6             try
  7             { 
  8                 #region 选择Excel文件进行处理 
  9             
 10                 //打开选择文件夹选择文件的对话框
 11                 FileDialog fd=new OpenFileDialog();  
 12                 fd.Title="请选择要处理的Excel文件";
 13                 
 14                 // filter过滤是成对的 如  Excel(*.xls)|*.xls|All File|*.*  
 15                 // 其中 选择 Excel(*.xls)时 过滤的是后面的*.xls的文件类型 选择All File 过滤的是后面的*.*
 16                 fd.Filter="Excel(*.xls)|*.xls|All File|*.*"; 
 17                 
 18                 // 选择要处理的Excel文件
 19                  if (fd.ShowDialog() == DialogResult.OK &&　!string.IsNullOrEmpty(fd.FileName)　)
 20                  {
 21                     string ExtensionStr=Path.GetExtension(fd.FileName); 
 22                      if (ExtensionStr!=".xls" && ExtensionStr!=".xlsx"  ) 
 23                      {
 24                         MessageBox.Show("请选择Excel文件"); return ;
 25                      }
 26                              
 27                     excelStr =fd.FileName; 
 28                     
 29                     setExcelFit(excelStr); 
 30                  
 31                     MessageBox.Show("处理成功!");
 32                  }
 33                 
 34                 #endregion
 35                 
 36                 #region 导出Excel保存时的处理 
 37                 
 38                 /*
 39                 //导出保存时的处理
 40                 // 保存时选择的文件夹对话框 
 41                  
 42                 string strExportFilePath = "";
 43                 
 44                   FolderBrowserDialog dlgFolder = new FolderBrowserDialog();  
 45                 dlgFolder.Description = "请选择要保存Excel文件的地址";  
 46                 dlgFolder.ShowNewFolderButton = true;
 47                 
 48                 if (dlgFolder.ShowDialog() == DialogResult.OK)
 49                 {
 50                     strExportFilePath = dlgFolder.SelectedPath;
 51 
 52                     excelStr = strExportFilePath + "\\计量信息" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
 53                     
 54                     //直接导出后再处理 (由于在此导出时是控件内部方法不能重改，就需要在导出后在进行处理)
 55                     this.grvMeasuremain.ExportToXls(excelStr); 
 56 
 57                     setExcelFit(excelStr); 
 58                      
 59                     MessageBox.Show("导出成功!");
 60                 }
 61                 */
 62                 
 63                 #endregion
 64 
 65             }
 66             catch (Exception expt)
 67             { 
 68                 MessageBox.Show("导出报表失败！" + expt.Message);
 69             }
 70         }
 71         
 72         public static void setExcelFit(string excelStr)
 73         {
 74             Microsoft.Office.Interop.Excel.Application m_objExcel = null;
 75             m_objExcel = new Microsoft.Office.Interop.Excel.Application();
 76 
 77             if (m_objExcel == null)
 78             {
 79                 MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
 80                 return;
 81             }
 82             Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null;
 83             Microsoft.Office.Interop.Excel.Workbook m_objBook = null;
 84             Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
 85             Microsoft.Office.Interop.Excel.Worksheet m_objSheet = null;
 86             Microsoft.Office.Interop.Excel.Range m_objRange = null;
 87 
 88             try
 89             {
 90                 m_objBooks = m_objExcel.Workbooks;
 91                 m_objBooks.Open(excelStr, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 92                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 93                     Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 94 
 95                 m_objBook = (Microsoft.Office.Interop.Excel.Workbook)m_objBooks.get_Item(1);
 96 
 97                 m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
 98 
 99                 m_objExcel.Visible = false;//不显示直接显示Excel
100 
101                 m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objSheets.get_Item(1);
102 
103                 m_objSheet.Cells.EntireColumn.AutoFit();//进行自动列宽处理
104         
105                 m_objRange = m_objSheet.get_Range("A1", "H30");//选定单元格
106 
107                 m_objRange.NumberFormatLocal = "G/通用格式"; //设置单元格格式为常规(有时导出的数据没有小数点而默认为整数，就是导出后单元格默认格式为自定义取整数)
108 
109                 m_objBook.Application.ActiveWorkbook.Save();  //保存 
110 
111             }
112             catch (Exception ex)
113             {
114                 MessageBox.Show(ex.Message);
115             }
116             finally
117             { 
118                 m_objExcel.Quit();//退出excle进程的占用 
119                 m_objRange = null;
120                 m_objBooks = null;
121                 m_objBook = null;
122                 m_objSheet = null;
123                 m_objSheets = null;
124                 m_objExcel = null;  
125                 GC.Collect(); //强制垃圾回收
126             }
127         } 
128 }
129 
130 void Main()
131 {
132     ExcelMake.reportByExcel();
133 }

 

转载于:https://www.cnblogs.com/luqx/archive/2012/06/07/2540354.html