操作Excel文件，读取，创建
读取Excel文件/// <summary>/// 创建excel/// </summary>/// <param name="regionListNameCn"></param>/// <param name="regionListNoMatch"></param>private void CreateExcel(IList<RegionInfo> regionListNameCn, IList<RegionInfo> regionListNoMatch){if (regionListNameCn.Count < 1 && regionListNoMatch.Count < 1){return;}string[] myHeader = { "城市英文名", "城市中文名", "国家英文名" };int rowCount = 1;int columnCount = 3;Application excelApp = new Application();Workbook myWorkBook = excelApp.Workbooks.Add(System.Reflection.Missing.Value);Range myRange = null;//第一个工作区：非法城市中文名数据Worksheet myWorkSheet = myWorkBook.Worksheets[1];//设置主标题myRange = myWorkSheet.Range["A1", "C1"];myRange.MergeCells = true;myRange.HorizontalAlignment = XlVAlign.xlVAlignCenter; // 格式为居中myRange.Interior.ColorIndex = 15;myRange.Font.Bold = true;myWorkSheet.Cells[1, 1] = "非法城市中文名数据";//设置副标题myRange = myWorkSheet.Range["A2", "C2"];myRange.Value2 = myHeader;myRange.Columns.AutoFit(); // 自适应宽度myRange.HorizontalAlignment = XlVAlign.xlVAlignCenter; // 格式为居中myRange.Font.Bold = true;int i = 3;foreach (RegionInfo region in regionListNameCn){//注意表格中的Cells索引是从1开始的myWorkSheet.Cells[i, 1] = region.NameEn;myWorkSheet.Cells[i, 2] = region.NameCn;myWorkSheet.Cells[i, 3] = region.CountryNameEn;i++;}rowCount = i - 1;//全局自适应宽度myWorkSheet.Range[excelApp.Cells[1, 1], excelApp.Cells[rowCount, columnCount]].Columns.AutoFit();//第二工作区：未导入到留学网的城市数据Worksheet myWorkSheet2 = myWorkBook.Worksheets[2];//设置主标题myRange = myWorkSheet2.Range["A1", "C1"];myRange.MergeCells = true;myRange.HorizontalAlignment = XlVAlign.xlVAlignCenter;myRange.Interior.ColorIndex = 15;myRange.Font.Bold = true;myWorkSheet2.Cells[1, 1] = "未导入到留学网的城市数据";//设置副标题myRange = myWorkSheet2.Range["A2", "C2"];myRange.Value2 = myHeader;myRange.Columns.AutoFit(); // 自适应宽度myRange.HorizontalAlignment = XlVAlign.xlVAlignCenter; // 格式为居中myRange.Font.Bold = true;i = 3;foreach (RegionInfo region in regionListNoMatch){myWorkSheet2.Cells[i, 1] = region.NameEn;myWorkSheet2.Cells[i, 2] = region.NameCn;myWorkSheet2.Cells[i, 3] = region.CountryNameEn;i++;}//全局自适应宽度(报错不知原因)//myWorkSheet2.Range[excelApp.Cells[1, 1], excelApp.Cells[rowCount, columnCount]].Columns.AutoFit();//excelApp.Visible = true;//保存Excel文件string realPathFile = GetExcelFileSaveRealPath();myWorkBook.SaveCopyAs(realPathFile);myWorkBook.Close(false, null, null);excelApp.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkBook);System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkSheet);System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkSheet2);myWorkBook = null;excelApp = null;myWorkSheet = myWorkSheet2 = null;//将保存好的Excel文件 传回客户端FileInfo file = new FileInfo(realPathFile);HttpContext.Current.Response.Clear();HttpContext.Current.Response.Charset = "GB2312";HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;//添加头信息，为"文件下载/另存为"对话框指定默认文件名HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);// 添加头信息，指定文件大小，让浏览器能够显示下载进度HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());// 指定返回的是一个不能被客户端读取的流，必须被下载HttpContext.Current.Response.ContentType = "application/ms-excel";// 把文件流发送到客户端HttpContext.Current.Response.WriteFile(file.FullName);HttpContext.Current.Response.Flush();File.Delete(file.FullName);}//获取一个服务器真实的存储excel文件地址及文件名private string GetExcelFileSaveRealPath(){string virtualPath = _uploadFilePath + DateTime.Now.ToString("yyyyMM") + "/";string realPath = HttpContext.Current.Server.MapPath(virtualPath);if (!Directory.Exists(realPath)){Directory.CreateDirectory(realPath);}//GetNewVirtualFilePath方法//根据给定的虚拟路径及文件扩展名，获取一个以当前时间为文件名的完整虚拟文件名，确保新的虚拟文件名不存在真实文件。string virtualFile = FileHelper.GetNewVirtualFilePath(virtualPath, ".xlsx");return HttpContext.Current.Server.MapPath(virtualFile);}
