对导出Excel的自动列宽的处理
在系统中往往有导出Excel文档,其中的列宽不能自适应给用户带来不方便需要手动进行设置,因此做了个列宽自适应的方法,作为日志记录
注意:需要引用 Microsoft.Office.Interop.Excel
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }