excel的导出导入的总结

最新推荐文章于 2024-05-06 08:53:54 发布

loveheronly

最新推荐文章于 2024-05-06 08:53:54 发布

阅读量714

点赞数 1

分类专栏： asp.net-- Web

本文链接：https://blog.csdn.net/loveheronly/article/details/53057180

版权

asp.net-- Web 专栏收录该内容

53 篇文章 0 订阅

订阅专栏

1.首先来谈谈导出

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
           
            worksheet.Cells[1, 1] = "用户代码";
            worksheet.Cells[1, 2] = "用户名称";
            worksheet.Cells[1, 3] = "绩效积分项";

            //先把下拉框值赋值在Z列上
            worksheet.Cells[1, 26] = "a";
            worksheet.Cells[2, 26] = "b";
            worksheet.Cells[3, 26] = "c";
            //在把Z列值赋值到下拉框列上
            worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[10000, 3]).Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, "=Sheet2!$A$1:$A$30", Type.Missing);
            
            //最后隐藏Z列
            Excel.Range ranger = (Excel.Range)worksheet.Columns["Z:Z", System.Type.Missing];
            ranger.Hidden = true;、
            workbook.SaveCopyAs("E:\\test.xls");
            workbook.Saved = true;

该写excel的导出并非是标准格式的excel，需要将Excel转换为标准的。如果用该报表再用代码读取会报错的，会提示“外部表不是预期的格式”的错误；

2.转换为标准的excel

        public  void ConvertExcel(string savePath)
        {
            //将xml文件转换为标准的Excel格式 
            Object Nothing = Missing.Value;//由于yongCOM组件很多值需要用Missing.Value代替   
            Excel.Application ExclApp = new Excel.ApplicationClass();// 初始化
            Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(savePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);//打开Excl工作薄   
            try
            {
                Object format = Excel.XlFileFormat.xlWorkbookNormal;//获取Excl 2007文件格式   
                ExclApp.DisplayAlerts = false;
                ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);//保存为Excl 2007格式   
            }
            catch (Exception ex) { }
            ExclDoc.Close(Nothing, Nothing, Nothing);
            ExclApp.Quit();
        }

3.读取excel这时候就正常了，读取大家都会的了，这里就说一下链接语句的区别

//2003（Microsoft.Jet.Oledb.4.0）
string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", excelFilePath);

//2010（Microsoft.ACE.OLEDB.12.0）
string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'",