安装
工具栏->NuGet 包管理器->管理解决方案的NuGet程序包
安装到当前项目中
使用
将 DataGridView 数据导出为Excel
- 首先,需要将数据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导出为Excel
ExcelDataConvert.DataToExcel(dt);
}
- 将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.配置Eplus
ExcelPackage.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 ,正常 1
int rs = conn.AddGame(game);
if (rs < 0) {
return; }
importCounts++;
MessageBox.Show(game.GameName + "导入成功!");
}
MessageBox.Show("共计导入"+importCounts);
}