Excel 转sql

来源：互联网发布：二级域名解析到端口编辑：程序博客网时间：2024/05/16 15:23

#region Excelzhuan DataTable
/// <summary>
/// Excel某sheet中内容导入到DataTable中
/// 区分xsl和xslx分别处理
/// </summary>
/// <param name="filePath">Excel文件路径,含文件全名</param>
/// <param name="sheetName">此Excel中sheet名</param>
/// <returns></returns>
public DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
{

DataTable dt = new DataTable();

if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
{//.xls
#region .xls文件处理:HSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{

hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}

ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

//一行最后一个方格的编号即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);

dt.Columns.Add(cell.ToString());
}

while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();

if (row.RowNum == 0) continue;//The firt row is title,no need import

for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}

ICell cell = row.GetCell(i);

if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}

if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}

dt.Rows.Add(dr);
}
#endregion
}
else
{//.xlsx
#region .xlsx文件处理:XSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{

hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}

ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);

//一行最后一个方格的编号即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);

dt.Columns.Add(cell.ToString());

}

while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();

if (row.RowNum == 0) continue;//The firt row is title,no need import

for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}

ICell cell = row.GetCell(i);

if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}

if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
return dt;
}
#endregion

#region Xls转DataTable
/// <summary>
/// Xls转DataTable
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public DataTable XlsToDataTable(string filePath, string sheetName)
{
DataTable dt = new DataTable();
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{

hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

ISheet sheet = hssfworkbook.GetSheet(sheetName);
IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

//一行最后一个方格的编号即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);

dt.Columns.Add(cell.ToString());

}

while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();

if (row.RowNum == 0) continue;//The firt row is title,no need import

for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}

ICell cell = row.GetCell(i);

if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}

if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}

return dt;

}
#endregion

#region 获取Excel 文件中Sheet列表
/// <summary>
/// 获取Excel 文件中Sheet列表
/// </summary>
/// <param name="excelFilename">excel文件名，完整路径文件名</param>
/// <returns>返回Excel的Sheet名列表</returns>
public List<string> GetExcelTableNames(string excelFilename)
{
try
{
string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", excelFilename);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable table = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (table != null)
{
List<string> tableLists = new List<string>(table.Rows.Count);

for (int i = 0; i < table.Rows.Count; i++)
{

tableLists.Add(table.Rows[i]["Table_Name"].ToString());

}

//过滤掉无用的表
for (int i = 0; i < tableLists.Count; i++)
{
if (tableLists[i].Contains("$"))
{
//过滤掉带有$的无用表
tableLists.Remove(tableLists[i]);
}
}

connection.Close();
return tableLists;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return null;
}
#endregion

0 0