导入excel精华版
//须引入 NPOI， NPOI.OOXML， NPOI.Openxml4Net， NPOI.OpenxmlFormats等程序集 自己去下载吧 NPOI组件很好用不可能下不到自己去吧，通常去百度网盘搜索引擎就有很多了
1添加ImportExcelHelper，ExcelDataMap，DataTypeDimesion等帮助类代码如下：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Holworth.Utility;
using NPOI.HSSF.Record;
namespace ConsoleApplication25
{
public enum DataTypeDimesion
{
浮点型 = 0,
整型 = 1,
日期 = 2,
字符串 = 3
}
public class ExcelDataMap
{
public int MapIndex { get; set; }
public string ColumnName { get; set; }
public DataTypeDimesion DataType { get; set; }
}
class ImportExcelHelper
{
private static string ColNameMapValue(DataTable table, int rowIndex, int colIndex)
{
return table.Rows[rowIndex][colIndex].ToString();
}
/// <summary>
/// 通过异或将表格的行列转置
/// </summary>
/// <param name="a"></param>
/// <param name="b"></param>
public static void Swap(ref int a, ref int b)
{
a = a ^ b;//a=10100,b=0010,两者相异或的结果是10110,并保存在a中。
b = a ^ b;//a=10110,b=0010,两者相异或的结果是10100，并保存在b中，换成二进制是20
a = a ^ b;//a=10110,b=10100,两者相异或的结果是00010，保存在a中，换成十进制是2
}
public static string NoTranspose(string fileName, List<ExcelDataMap> ExcelDataMaps, int startIndex,string importTableName)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds = ExcelHelper.ReadExcelAllSheets(fileName, 0, false);
dt = ds.Tables[0];
string columns = "";
string sql = "begin\n";
while (true)
{
string tempsql = "";
//最后一列遇到结束的时候跳出循环不再拼接sql语句
int MinIndex = ExcelDataMaps.Min(e => e.MapIndex);
if (startIndex < dt.Rows.Count && string.IsNullOrEmpty(dt.Rows[startIndex][MinIndex].ToString()))
{
bool pause = true;
for (int i = MinIndex+1; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[startIndex][i].ToString()))
{
pause = false;
}
}
if (pause)
{
break;
}
}
if (startIndex >= dt.Rows.Count)
{
break;
}
tempsql += string.Format("\n insert into {0}( ", importTableName);
ExcelDataMaps.ForEach(e => { columns += e.ColumnName + ","; });
//额外添加一个输入日期
//columns += "INPUT_DATE" + ",";
columns = columns.TrimEnd(',');
//sql 先拼接头部
tempsql += columns;
tempsql += ")";
tempsql += "\n";
tempsql += "values(";
foreach (ExcelDataMap dataMap in ExcelDataMaps)
{
int rowIndex = dataMap.MapIndex;
//回来
string valueObject = ColNameMapValue(dt, startIndex, rowIndex).Trim();
switch (dataMap.DataType)
{
case DataTypeDimesion.字符串:
tempsql += "'" + valueObject + "'" + ",";
break;
case DataTypeDimesion.整型:
int i = 0;
if (String.IsNullOrEmpty(valueObject))
{
valueObject = "0";
}
Int32.TryParse(valueObject, out i);
tempsql += i + ",";
break;
case DataTypeDimesion.浮点型:
decimal f = 0;
if (String.IsNullOrEmpty(valueObject))
{
valueObject = "0";
}
Decimal.TryParse(valueObject, out f);
tempsql += f + ",";
break;
case DataTypeDimesion.日期:
if (!String.IsNullOrEmpty(valueObject))
{
valueObject = (String.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')", valueObject));
}
else
{
valueObject = "''";
}
tempsql += valueObject + ",";
break;
}
}
//tempsql += (string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')", DateTime.Now)) + ",";
tempsql = tempsql.TrimEnd(',');
tempsql += ");";
sql += tempsql;
startIndex++;
columns = "";
}
sql += "\n end;";
return sql;
}
public static string IsTranspose(string fileName, List<ExcelDataMap> ExcelDataMaps, int startIndex, string importTableName)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds = ExcelHelper.ReadExcelAllSheets(fileName, 0, false);
dt = ds.Tables[0];
string columns = "";
string sql = "begin\n";
while (true)
{
string tempsql = "";
//最后一列遇到结束的时候跳出循环不再拼接sql语句
int MinIndex = ExcelDataMaps.Min(e => e.MapIndex);
if (startIndex < dt.Columns.Count && string.IsNullOrEmpty(dt.Rows[MinIndex][startIndex].ToString()))
{
bool pause=true;
for (int i = MinIndex+1; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][startIndex].ToString()))
{
pause = false;
}
}
if (pause)
{
break;
}
}
if (startIndex >= dt.Columns.Count)
{
break;
}
tempsql += string.Format("\n insert into {0}( ", importTableName);
ExcelDataMaps.ForEach(e => { columns += e.ColumnName + ","; });
//额外添加一个输入日期
//columns += "INPUT_DATE" + ",";
columns = columns.TrimEnd(',');
//sql 先拼接头部
tempsql += columns;
tempsql += ")";
tempsql += "\n";
tempsql += "values(";
string tempCompanyName = String.Empty;
foreach (ExcelDataMap dataMap in ExcelDataMaps)
{
int rowIndex = dataMap.MapIndex;
string valueObject = ColNameMapValue(dt, rowIndex, startIndex).Trim();
switch (dataMap.DataType)
{
case DataTypeDimesion.字符串:
tempsql += "'" + valueObject + "'" + ",";
break;
case DataTypeDimesion.整型:
int i = 0;
if (String.IsNullOrEmpty(valueObject))
{
valueObject = "0";
}
Int32.TryParse(valueObject, out i);
tempsql += i + ",";
break;
case DataTypeDimesion.浮点型:
decimal f = 0;
if (String.IsNullOrEmpty(valueObject))
{
valueObject = "0";
}
Decimal.TryParse(valueObject, out f);
tempsql += f + ",";
break;
case DataTypeDimesion.日期:
if (!String.IsNullOrEmpty(valueObject))
{
valueObject = (String.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')", valueObject));
}
else
{
valueObject = "''";
}
tempsql += valueObject + ",";
break;
}
}
//tempsql += (string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')", DateTime.Now)) + ",";
tempsql = tempsql.TrimEnd(',');
tempsql += ");";
sql += tempsql;
startIndex++;
columns = "";
}
sql += "\n end;";
return sql;
}
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="ExcelDataMaps"></param>
/// <param name="startIndex"> 不转置的情况代表从第几行开始读取，转置的情况代表从第几列开始读取(不转置每行代表一条记录，转置则每列代表一条记录)</param>
/// <param name="importTableName">导入的表名</param>
/// <param name="isTranspose"></param>
/// <returns></returns>
public static string ImportExcel4DataMap(string fileName, List<ExcelDataMap> ExcelDataMaps, int startIndex, string importTableName, bool isTranspose)
{
string sql = "";
if (isTranspose)
{
sql = IsTranspose(fileName, ExcelDataMaps, startIndex, importTableName);
}
else
{
sql = NoTranspose(fileName, ExcelDataMaps, startIndex, importTableName);
}
return sql;
}
}
}
2.中间帮助类 ExcelHelper.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
//using NPOI.HSSF.Record.Formula.Eval;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.SS;
using NPOI.DDF;
using NPOI.SS.Util;
using System.Collections;
using System.Text.RegularExpressions;
using NPOI.XSSF.UserModel;
namespace Holworth.Utility
{
public class ExcelHelper
{
#region 读取Excel第一个Sheet
public static DataTable ReadExcel(string path, int TitleRow, bool lastRowIsData)
{
string extname = System.IO.Path.GetExtension(path);
if (extname.ToLower().Equals(".xls"))
{
return ReadExcel2003(path, TitleRow, lastRowIsData, 0);
}
else
{
return ReadExcel2007(path, TitleRow, lastRowIsData, 0);
}
}
public static DataTable ReadExcel(string path, int TitleRow, bool lastRowIsData, int tableindex)
{
string extname = System.IO.Path.GetExtension(path);
if (extname.ToLower().Equals(".xls"))
{
return ReadExcel2003(path, TitleRow, lastRowIsData, tableindex);
}
else
{
return ReadExcel2007(path, TitleRow, lastRowIsData, tableindex);
}
}
private static DataTable ReadExcel2003(string path, int TitleRow, bool lastRowIsData, int tableindex)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook2003 = new HSSFWorkbook(file);
//获取excel的第一个sheet
HSSFSheet sheet = (HSSFSheet)workbook2003.GetSheetAt(tableindex);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = (HSSFRow)sheet.GetRow(TitleRow);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column.ToString().Trim().Replace(" ", ""));
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (TitleRow + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
workbook2003 = null;
sheet = null;
return table;
}
private static DataTable ReadExcel2007(string path, int TitleRow, bool lastRowIsData, int tableindex)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
XSSFWorkbook workbook2007 = new XSSFWorkbook(file);
//获取excel的第一个sheet
XSSFSheet sheet = (XSSFSheet)workbook2007.GetSheetAt(tableindex);
DataTable table = new DataTable();
//获取sheet的标题行，用于形成表头
XSSFRow headerRow = (XSSFRow)sheet.GetRow(TitleRow);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (TitleRow + 1); i < sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
workbook2007 = null;
sheet = null;
return table;
}
#endregion
#region 读取Excel所有Sheet
public static DataSet ReadExcelAllSheets(string path, int TitleRow, bool lastRowIsData)
{
string extname = System.IO.Path.GetExtension(path);
if (extname.ToLower().Equals(".xls"))
{
return ReadExcel2003Tables(path, TitleRow, lastRowIsData);
}
else
{
return ReadExcel2007Tables(path, TitleRow, lastRowIsData);
}
}
private static DataSet ReadExcel2003Tables(string path, int TitleRow, bool lastRowIsData)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook2003 = new HSSFWorkbook(file);
int sheetcount = workbook2003.NumberOfSheets;
DataSet retds = new DataSet();
for (int sheetindex = 0; sheetindex < sheetcount; sheetindex++)
{
//获取excel的每一个sheet
HSSFSheet sheet = (HSSFSheet)workbook2003.GetSheetAt(sheetindex);
DataTable table = new DataTable();
//获取sheet的首行
HSSFRow headerRow = (HSSFRow)sheet.GetRow(TitleRow);
if (headerRow == null)
{
continue;
}
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = 0; i < cellCount; i++)
{
try
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column.ToString().Trim().Replace(" ", ""));
}
catch {
table.Columns.Add();
}
}
if (table.Columns.Count == 0)
{
continue;
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (TitleRow + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = 0; j < cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
catch
{
dataRow[j] = DBNull.Value
;
}
}
table.Rows.Add(dataRow);
}
table.TableName = sheet.SheetName;
retds.Tables.Add(table);
sheet = null;
}
workbook2003 = null;
return retds;
}
private static DataSet ReadExcel2007Tables(string path, int TitleRow, bool lastRowIsData)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
XSSFWorkbook workbook2007 = new XSSFWorkbook(file);
int sheetcount = workbook2007.NumberOfSheets;
DataSet retds = new DataSet();
for (int sheetindex = 0; sheetindex < sheetcount; sheetindex++)
{
//获取excel的每一个sheet
XSSFSheet sheet = (XSSFSheet)workbook2007.GetSheetAt(sheetindex);
DataTable table = new DataTable();
//获取sheet的标题行，用于形成表头
XSSFRow headerRow = (XSSFRow)sheet.GetRow(TitleRow);
if (headerRow == null)
{
continue;
}
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = 0; i < cellCount; i++)
{
try
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
catch
{
table.Columns.Add();
}
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (TitleRow + 1); i < sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = 0; j < cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
catch
{
dataRow[j] = DBNull.Value
;
}
}
table.Rows.Add(dataRow);
}
table.TableName = sheet.SheetName;
retds.Tables.Add(table);
sheet = null;
}
workbook2007 = null;
return retds;
}
#endregion
}
}
3.调用示例
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Holworth.Utility;
using Microsoft.SqlServer.Server;
namespace ConsoleApplication25
{
class Program
{
static void Main(string[] args)
{
//转置 导入
#region 转置 导入
//string sql = ImportExcelHelper.ImportExcel4DataMap(@"D:\data of office.xls", new List<ExcelDataMap>()
//{
// //EXCEL 列映射 start
// (new ExcelDataMap() { MapIndex = 0, ColumnName = "CLIENT_NAME", DataType = DataTypeDimesion.字符串 }),
// (new ExcelDataMap() { MapIndex = 1, ColumnName = "COMPANY_NAME", DataType = DataTypeDimesion.字符串 }),
// (new ExcelDataMap() { MapIndex = 3, ColumnName = "TOTAL_ASSET", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 4, ColumnName = "SALE_REVENUE", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 6, ColumnName = "RETURN_ON_EQUITY", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 7, ColumnName = "SALE_PROFIT", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 8, ColumnName = "RETURN_TOTAL_ASSET", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 9, ColumnName = "INVENTORY_TURNOVER", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 10, ColumnName = "FIXED_ASSET_TURNOVER", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 11, ColumnName = "TOTAL_ASSET_TUNOVER", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 13, ColumnName = "ACCOUNT_RECEIVABLE_TURNOVER", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 14, ColumnName = "CURRENT_RATIO", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 15, ColumnName = "QUICK_RATIO", DataType = DataTypeDimesion.浮点型 }),
// (new ExcelDataMap() { MapIndex = 16, ColumnName = "ASSET_LIABILITY_RATIO", DataType = DataTypeDimesion.浮点型 }),
// //EXCEL 列映射 end
//}, 0, "ccr_credit_raw_data", true);
#endregion
//转置导入
#region 无转置 导入
string sql2 = ImportExcelHelper.ImportExcel4DataMap(@"D:\aa.xls", new List<ExcelDataMap>()
{
//EXCEL 列映射 start
(new ExcelDataMap() { MapIndex = 0, ColumnName = "CLIENT_NAME", DataType = DataTypeDimesion.字符串 }),
(new ExcelDataMap() { MapIndex = 1, ColumnName = "COMPANY_NAME", DataType = DataTypeDimesion.字符串 }),
(new ExcelDataMap() { MapIndex = 2, ColumnName = "TOTAL_ASSET", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 3, ColumnName = "SALE_REVENUE", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 4, ColumnName = "RETURN_ON_EQUITY", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 5, ColumnName = "SALE_PROFIT", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 6, ColumnName = "RETURN_TOTAL_ASSET", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 7, ColumnName = "INVENTORY_TURNOVER", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 8, ColumnName = "FIXED_ASSET_TURNOVER", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 9, ColumnName = "TOTAL_ASSET_TUNOVER", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 10, ColumnName = "ACCOUNT_RECEIVABLE_TURNOVER", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 11, ColumnName = "CURRENT_RATIO", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 12, ColumnName = "QUICK_RATIO", DataType = DataTypeDimesion.浮点型 }),
(new ExcelDataMap() { MapIndex = 13, ColumnName = "ASSET_LIABILITY_RATIO", DataType = DataTypeDimesion.浮点型 }),
//EXCEL 列映射 end
}, 0, "ccr_credit_raw_data", false);
#endregion
}
}
}
