EPPlus库的安装和使用 C# 中 Excel的导入和导出

安装

工具栏->NuGet 包管理器->管理解决方案的NuGet程序包

安装到当前项目中

使用

将 DataGridView 数据导出为Excel

1. 首先，需要将数据DataGridView对象转换为DataTable

private void btnExport_Click(object sender, EventArgs e)
{// 1.将当前页面的dataGridView获取并且转换为datatable对象用于转换数据DataTable dt = new DataTable();// 创建一个新的DataTable，并添加列  for (int i = 0; i < dvgame.ColumnCount; i++){dt.Columns.Add(dvgame.Columns[i].HeaderText);}// 添加行数据  for (int i = 0; i < dvgame.RowCount; i++){DataRow dr = dt.NewRow();for (int j = 0; j < dvgame.ColumnCount; j++){dr[j] = dvgame.Rows[i].Cells[j].Value.ToString();}dt.Rows.Add(dr);}// 2.使用工具类，将datatable导出为ExcelExcelDataConvert.DataToExcel(dt);
}

2. 将DataTable传入工具类中，导出为Excel

public static void DataToExcel(DataTable dataTable)
{// 1.创建一个Excel文件ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 使用非商业许可ExcelPackage excelPackage = new ExcelPackage();// 2.创建一个ExcelSheet 工作表  ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");// 3.将数据写入到Excel中worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);// 4.保存文件 保存Excel文件  string dictFolder = "C:\\Users\\robin\\Desktop\\csharpExcels";DateTime currentTime = DateTime.Now;string year = "\\" + currentTime.Year.ToString();string month = "\\" + currentTime.Month.ToString();string day = "\\" + currentTime.Day.ToString();dictFolder += year + month + day;// 判断文件夹是否存在，如果不存在则递归创建if (!Directory.Exists(dictFolder)){Directory.CreateDirectory(dictFolder);}string fileName = "\\" + DateTime.Now.ToString("hh-mm-ss")+ "games.xlsx";string filePath = dictFolder + fileName;if (File.Exists(filePath)){File.Delete(filePath);}excelPackage.SaveAs(new FileInfo(filePath));// Console.WriteLine("Excel文件已导出！");MessageBox.Show("Excel文件已经导出！路径为"+filePath);
}

将Excel导入到数据库中

导入数据无非就是，读取数据，以及Sql注入

public static void ExcelToDataGame(string filepath)
{// 1.配置EplusExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 使用非商业许可，如有需要请更改  ExcelPackage package = new ExcelPackage(new FileInfo(filepath));ExcelWorksheet worksheet = package.Workbook.Worksheets[0];  // 选择第一个工作表  int rowCount = worksheet.Dimension.Rows;  // 获取行数  int colCount = worksheet.Dimension.Columns;  // 获取列数  // 2.获取数据库对象GameDataAccess conn =  GameDataAccess.GetInstance();int importCounts = 0;for (int row = 1; row <= rowCount; row++){if (row == 1){continue;}// 3.每次读取一行，执行一行SQl的插入string sql = "";GameEntity game = new GameEntity();game.GameName = worksheet.Cells[row, 1].Value.ToString();game.BackImg = worksheet.Cells[row, 2].Value.ToString();game.AddTime = DateTime.ParseExact(worksheet.Cells[row, 3].Value.ToString(), "yyyy/M/d H:mm:ss", null);game.Price = decimal.Parse(worksheet.Cells[row, 4].Value.ToString());game.Issue = worksheet.Cells[row, 5].Value.ToString();game.Type = worksheet.Cells[row, 6].Value.ToString();game.Describe = worksheet.Cells[row, 7].Value.ToString();game.Status = worksheet.Cells[row, 8].Value.ToString() == "下架" ? 0 : 1; //下架 0 ，正常 1int rs = conn.AddGame(game);if (rs < 0) { return; }importCounts++;MessageBox.Show(game.GameName + "导入成功!");}MessageBox.Show("共计导入"+importCounts);
}