C# 读写 excel (它支持html格式的excel文件)

秒速五厘米 2022-09-18 14:57 182阅读 0赞

这是通过微软office导出excel文件,为什么通过微软的office导出呢，因为我开始用的是npoi office导出，它不支持一种格式（就是html格式的excel文件）,但是office支持，所以，写个博客，给大家分享，分享

思维方式:通过读取文件的excel文件，再通过模版格式导出相应的格式

/// <summary>
            /// 导出excel
            /// </summary>
            /// <param name="readFileName">读取文件路径</param>
            /// <param name="saveFileName">保存文件内容</param>
            public static void ReadFile(string readFileName, string saveFileName)
            {
                Microsoft.Office.Interop.Excel.WorkbookClass wbclass;
                WorkbookClass wkTemp;
                Worksheet ws;
                Worksheet wsTemp;
                Microsoft.Office.Interop.Excel.Application excelApp;
                DirectoryInfo di = new DirectoryInfo(readFileName);
    
                //遍历文件夹            
                if (di.Exists)
                {
                    foreach (FileInfo item in di.GetFiles("*.xls"))
                    {
                        excelApp = new Microsoft.Office.Interop.Excel.Application();
                        object objOpt = System.Reflection.Missing.Value;
                        //item.FullName   读取execl文件
                        wbclass = (WorkbookClass)excelApp.Workbooks.Open(item.FullName, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
                        ws = (Worksheet)wbclass.Worksheets.get_Item(1);
                        //取得总记录行数   (包括标题列)
                        int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
                        //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数
    
                        Range rng1 = ws.Cells.get_Range("C1", "C" + rowsint);  //rang
                        Range rng2 = ws.Cells.get_Range("D1", "D" + rowsint); //rang
                        Range rng3 = ws.Cells.get_Range("E1", "E" + rowsint); //rang 
                        Range rng4 = ws.Cells.get_Range("F1", "F" + rowsint); //rang
    
                        object[,] CallType = (object[,])rng1.Value2;
                        object[,] ActiveCall = (object[,])rng2.Value2;
                        object[,] PassivityCall = (object[,])rng3.Value2;
                        object[,] CallDate = (object[,])rng4.Value;
    
                        //通过模版读取
                        wkTemp = (WorkbookClass)excelApp.Workbooks.Open("D:\\Template.xls", objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
                        wsTemp = (Worksheet)wkTemp.Worksheets.get_Item(1);
    
                        Range rngD = (Range)wsTemp.Columns["D", Type.Missing];//设置单元格格式
                        Range rngE = (Range)wsTemp.Columns["E", Type.Missing];//设置单元格格式
    
                        rngD.NumberFormat = "yyyy-mm-dd hh:mm:ss";
                        rngE.NumberFormat = "hh:mm:ss";
                        //通过模版写入信息excel文件
                        for (int i = 1; i < rowsint; i++)
                        {
                            wsTemp.Cells[i, 1] = CallType[i, 1].ToString();
                            wsTemp.Cells[i, 2] = ActiveCall[i, 1].ToString();  
                            wsTemp.Cells[i, 3] = PassivityCall[i, 1].ToString();
                            wsTemp.Cells[i, 4] = PassivityCall[i, 1].ToString();
                        }
                        wsTemp.SaveAs(saveFileName + "\\1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                        excelApp.Workbooks.Close();//关闭打开的文档 否则学号会显示科学计数法。 
                        excelApp.Quit();
                    }
                }
            }