C#Excelテーブルからデータを読み込んでDataTableオブジェクトに格納DataTableからデータベースSQL SERVERに格納
ExcelUtilツールクラス
DataTableからSQL SERVERに格納
#region Excel Datable
///
/// Excel Datable
///
/// ( )
///
public static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook XLSX ,HSSFWorkbook XLS
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null)
{
return null;
}
// Sheet
ISheet sheet = workbook.GetSheetAt(0);
//
IRow header = sheet.GetRow(sheet.FirstRowNum);
List columns = new List();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
//
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
///
///
///
///
///
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
DataTableからSQL SERVERに格納
///
/// DataTable
///
/// DataTable
///
/// DataTable
///
public static int SqlbulkcopyInsert(DataTable dt, string tableName, Dictionary dic)
{
try
{
// string defaultConnectionString = ConfigurationManager.ConnectionStrings["mainDB"].ConnectionString;
// defaultConnectionString
using (SqlBulkCopy bcp = new SqlBulkCopy(defaultConnectionString))
{
//
bcp.DestinationTableName = tableName;
foreach (KeyValuePair kv in dic)
{
bcp.ColumnMappings.Add(kv.Key, kv.Value);
}
bcp.WriteToServer(dt);
}
return 1;
}
catch (Exception e)
{
return 0;
}
finally
{
}
}