C#导出和导入Excel模板功能

 

引用  Aspose.Cells;

基于WinForm

导入

private void btn_excel_input_Click(object sender, EventArgs e)
{
try
{

DataTable dt = new DataTable();

string filePath = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Microsoft Excel files(*.xls)|*.xls"; //过滤一下，只要表格格式的
ofd.InitialDirectory = "c:\\";
ofd.RestoreDirectory = true;
ofd.FilterIndex = 1;
ofd.AddExtension = true;
ofd.CheckFileExists = true;
ofd.CheckPathExists = true;
ofd.ShowHelp = true; //是否显示帮助按钮

 

if (ofd.ShowDialog() == DialogResult.OK)
{
size_editfalge = "0";
thinkss_editflage = "0";
door_editflage = "0";
filePath = ofd.FileName;

Workbook workbook = new Workbook();

workbook.Open(filePath);

for (int i = 0; i < workbook.Worksheets.Count; i++)
{
//取第一个表
Cells cells = workbook.Worksheets[i].Cells;
if (cells.MaxDataRow != 0)
{
dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, 导入EXCEL的列数);
dt =dt_handle(dt);
//获取到了数据，存储到表中

bool k = insert_into_db(dt);
if (k)
{
MessageBox.Show("导入成功，重新查询即可看到！");

}
else
{

MessageBox.Show("导入失败！");

}
}

}

}

}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

 

导出

private void button3_Click(object sender, EventArgs e)
{

try
{

DataTable dt = (DataTable)dataGridView1.DataSource;
string fileToSave = "";
SaveFileDialog sfd = new SaveFileDialog();
sfd.InitialDirectory = "C:\\";
sfd.Filter = "Excel文件(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
fileToSave = sfd.FileName;
}

if (fileToSave == "")
{
return;
}

#region Excel设置数据源

WorkbookDesigner designer = new WorkbookDesigner();
string path = Application.StartupPath + "\\存储的文件夹\\文件名.xls";  //加载报表模板的存储地址
designer.Open(path);

dt.TableName = "A";  //EXCEL模板报表的别名
designer.SetDataSource(dt);

designer.Process();
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Save(fileToSave, FileFormatType.Excel2);
MessageBox.Show("报表导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);



}
catch(Exception ex)
{

MessageBox.Show(ex.ToString());

}


}

报表模板样式

列名1                                                                                           列名2                                                                列名3

&=[A].对应DataTable的列名1                                     &=[A].对应DataTable的列名2                               &=[A].对应DataTable的列名3