最近一个扫描二维码的工具,并保存的数据库中,导出Excel的文件,
下面是导出Excel的代码,经过测试
// 文件路径
string mdbFilePath = Application.StartupPath + @"\MDB_Test.mdb";
string excelFilePath = Application.StartupPath + @"\MDB_Test.xlsx";
string connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={mdbFilePath};";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM Serial_list", connection);
int colNumber = command.ExecuteNonQuery();
using (OleDbDataReader reader = command.ExecuteReader())
{
//建立数据库,并初始化
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
Excel.Worksheet worksheet = null;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// sheet 名称
worksheet.Name = "ExportedData";
int rowIndex = 1;
while (reader.Read() ) //读取数据库中数据
{
for (int i = 0; i < reader.FieldCount; i++)
{
worksheet.Cells[rowIndex, i + 1] = reader.GetValue(i)?.ToString();
}
rowIndex++;
}
reader.Close(); //读数据结束
if (worksheet != null)
{
// 调整列宽以适应内容
worksheet.Columns.AutoFit();
if (System.IO.File.Exists(excelFilePath))
{
System.IO.File.Delete(excelFilePath);
}
// 保存Excel文件
workbook.SaveAs(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(false, Type.Missing, Type.Missing);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
worksheet = null;
workbook = null;
excelApp = null;
MessageBox.Show("Excel file 导出成功!");
}
connection.Close();
}
}
导出结果: