1、将选中的Excel文件转换成DataTable
/// <summary>
/// 导入
/// </summary>
/// <param name="filePath"></param>
/// <param name="dgv"></param>
public static DataTable GetExcelData(string str)
{
DataTable dt = new DataTable();
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+str+";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
string strExcel = "select * from [sheet1$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(dt);
}
return dt;
}
例如:
private void btnLead_Click(object sender, EventArgs e)
{
OpenFileDialog opd = new OpenFileDialog();
if (opd.ShowDialog() == DialogResult.OK)
{
string FileName = opd.FileName;
DataTable dt = GetExcelData(FileName);
}
}
2、将DataTable导出
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
public static void ExportToExcel(DataTable dt, List<string> header)
{
if (dt == null)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "MicroSoft Excel 文件|*.xlsx;";
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells;//单元格
Style styleHeader = workbook.Styles[workbook.Styles.Add()];//新增样式
styleHeader.HorizontalAlignment = TextAlignmentType.Center;//文字居中
styleHeader.Font.IsBold = true;//粗体
styleHeader.IsTextWrapped = true;//单元格内容自动换行
//生成表头
for (int i = 0; i < header.Count; i++)
{
cells[0, i].PutValue(header[i]);
cells[0, i].SetStyle(styleHeader);
}
//生成表体
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < header.Count; j++)
{
cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());
}
}
workbook.Save(sfd.FileName, FileFormatType.Excel2007Xlsx);
MessageBox.Show("导出完成");
}
例如:
private void ExportDetail_Click(object sender, EventArgs e)
{
DataTable dtdata = grdList.DataSource as DataTable;
if (dtdata == null)
{
return;
}
if (AppUtil.Confirm("是否确认导出明细表"))
{
List<string> header = new List<string>();
header.Add("表头名1");
header.Add("表头名2");
header.Add("表头名3");
header.Add("表头名4");
header.Add("表头名5");
dtdata.Columns["对应表头名1字段"].SetOrdinal(0);
dtdata.Columns["对应表头名2字段"].SetOrdinal(1);
dtdata.Columns["对应表头名3字段"].SetOrdinal(2);
dtdata.Columns["对应表头名4字段"].SetOrdinal(3);
dtdata.Columns["对应表头名5字段"].SetOrdinal(4);
AppUtil.ExportToExcel(dtdata, header);
}
}