C# excel导入
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace MesQC.QualityData
{
public class ExcelHelper
{
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="headerIndex">列头的下标</param>
/// <param name="colNum">总共多少列</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToTable(string fileName, Stream fs, bool isFirstRowColumn, string sheetName, int headerIndex, int colNum)
{
IWorkbook workbook = null;
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(headerIndex);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (colNum != -1)
cellCount = colNum;
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
if (data.Columns.Contains(cellValue))
{
cellValue += i;
column = new DataColumn(cellValue);
data.Columns.Add(column);
}
else
{
data.Columns.Add(column);
}
}
}
}
startRow = headerIndex + 1;
}
else
{
//startRow = sheet.FirstRowNum;
startRow = sheet.FirstRowNum + 1;
}
// 计算公式
IFormulaEvaluator evaluator = null;
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
evaluator = new XSSFFormulaEvaluator(workbook);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
evaluator = new HSSFFormulaEvaluator(workbook);
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
// 过滤空行
if (row.GetCell(0) == null || row.GetCell(0).ToString().Equals("")) {
break;
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null)
{
string value = null;
ICell cell = row.GetCell(j);
//判断数据的类型
switch (cell.CellType)
{
case CellType.Numeric: //数字
cell.SetCellType(CellType.Numeric);
value = (cell.NumericCellValue).ToString();
break;
case CellType.String: //字符串
cell.SetCellType(CellType.String);
value = cell.StringCellValue;
break;
case CellType.Boolean: //Boolean
cell.SetCellType(CellType.Boolean);
value = (cell.BooleanCellValue).ToString();
break;
//case CellType.Formula: //公式
// evaluator.EvaluateInCell(cell);
// cell.SetCellType(CellType.String);
// value = cell.StringCellValue;
// break;
case CellType.Unknown: //空值
value = "";
break;
case CellType.Error: //故障
value = "非法字符";
break;
default:
row.GetCell(j).SetCellType(CellType.String);
value = row.GetCell(j).StringCellValue;
break;
}
// 科学计数法转正常数值
if (value.IndexOf("E") != -1)
{
double number;
try
{
bool flag = Double.TryParse(value, System.Globalization.NumberStyles.Float, null, out number);
if (flag)
{
value = number.ToString();
}
else {
row.GetCell(j).SetCellType(CellType.String);
value = row.GetCell(j).StringCellValue;
}
}
catch (Exception e)
{
}
}
if (value.Equals("#N/A")) {
value = "";
}
dataRow[j] = value;
}
else
{
dataRow[j] = null;
}
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
/// <summary>
/// 使用反射将DataTable转换成List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
public static List<T> DataTable2List<T>(DataTable data)
{
List<T> results = new List<T>();
Type type = typeof(T);
foreach (DataRow row in data.Rows)
{
T o = (T)Activator.CreateInstance(type);
var props = type.GetProperties();
foreach (var prop in props)
{
// 获取到属性上的 ColumnOrderAttribute 特性
object[] attributes = prop.GetCustomAttributes(typeof(ColumnOrderAttribute), false);
// ColumnOrderAttribute 特性存在
if (attributes.Length > 0)
{
ColumnOrderAttribute orderAttribute = attributes[0] as ColumnOrderAttribute;
if (prop.PropertyType.FullName.Contains("DateTime"))
{
if (!row[orderAttribute.Order].ToString().Equals(""))
prop.SetValue(o, DateTime.Parse(row[orderAttribute.Order].ToString()), null);
}
if (prop.PropertyType.FullName.Contains("String"))
{
if (!row[orderAttribute.Order].ToString().Equals(""))
prop.SetValue(o, row[orderAttribute.Order].ToString(), null);
}
if (prop.PropertyType.FullName.Contains("Boolean"))
{
if (!row[orderAttribute.Order].ToString().Equals(""))
prop.SetValue(o, Boolean.Parse(row[orderAttribute.Order].ToString()), null);
}
}
}
results.Add(o);
}
return results;
}
public static int getRealRowNum(ISheet sheet) {
int rowNum = sheet.LastRowNum;
while(rowNum > 0 ){
IRow row = sheet.GetRow(rowNum);
if (row != null) {
if (row.GetCell(0) != null)
{
row.GetCell(0).SetCellType(CellType.String);
if (row.GetCell(0).StringCellValue != "") {
return rowNum;
}
}
}
rowNum--;
}
return rowNum;
}
}
}
SqlBulkCopy 批量插入
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
namespace MesUtil
{
public static class SqlConnectionExtension
{
/// <summary>
/// 使用 SqlBulkCopy 向 destinationTableName 表插入数据
/// </summary>
/// <typeparam name="TModel">必须拥有与目标表所有字段对应属性</typeparam>
/// <param name="conn"></param>
/// <param name="modelList">要插入的数据</param>
/// <param name="batchSize">SqlBulkCopy.BatchSize</param>
/// <param name="destinationTableName">如果为 null，则使用 TModel 名称作为 destinationTableName</param>
/// <param name="bulkCopyTimeout">SqlBulkCopy.BulkCopyTimeout</param>
/// <param name="externalTransaction">要使用的事务</param>
public static void BulkCopy<TModel>(this SqlConnection conn, List<TModel> modelList, int batchSize, string destinationTableName = null, int? bulkCopyTimeout = null, SqlTransaction externalTransaction = null)
{
bool shouldCloseConnection = false;
if (string.IsNullOrEmpty(destinationTableName))
destinationTableName = typeof(TModel).Name;
DataTable dtToWrite = ToSqlBulkCopyDataTable(modelList, conn, destinationTableName);
SqlBulkCopy sbc = null;
try
{
if (externalTransaction != null)
sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, externalTransaction);
else
sbc = new SqlBulkCopy(conn);
using (sbc)
{
sbc.BatchSize = batchSize;
sbc.DestinationTableName = destinationTableName;
if (bulkCopyTimeout != null)
sbc.BulkCopyTimeout = bulkCopyTimeout.Value;
if (conn.State != ConnectionState.Open)
{
shouldCloseConnection = true;
conn.Open();
}
sbc.WriteToServer(dtToWrite);
}
}
finally
{
if (shouldCloseConnection && conn.State == ConnectionState.Open)
conn.Close();
}
}
public static DataTable ToSqlBulkCopyDataTable<TModel>(List<TModel> modelList, SqlConnection conn, string tableName)
{
DataTable dt = new DataTable();
Type modelType = typeof(TModel);
List<SysColumn> columns = GetTableColumns(conn, tableName);
List<PropertyInfo> mappingProps = new List<PropertyInfo>();
var props = modelType.GetProperties();
for (int i = 0; i < columns.Count; i++)
{
var column = columns[i];
PropertyInfo mappingProp = props.Where(a => a.Name == column.Name).FirstOrDefault();
if (mappingProp == null)
throw new Exception(string.Format("model 类型 '{0}'未定义与表 '{1}' 列名为 '{2}' 映射的属性", modelType.FullName, tableName, column.Name));
mappingProps.Add(mappingProp);
Type dataType = GetUnderlyingType(mappingProp.PropertyType);
if (dataType.IsEnum)
dataType = typeof(int);
dt.Columns.Add(new DataColumn(column.Name, dataType));
}
foreach (var model in modelList)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < mappingProps.Count; i++)
{
PropertyInfo prop = mappingProps[i];
object value = prop.GetValue(model,null);
if (GetUnderlyingType(prop.PropertyType).IsEnum)
{
if (value != null)
value = (int)value;
}
dr[i] = value ?? DBNull.Value;
}
dt.Rows.Add(dr);
}
return dt;
}
static List<SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
{
string sql = string.Format("select syscolumns.name,syscolumns.colorder from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);
List<SysColumn> columns = new List<SysColumn>();
using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())
{
SqlCommand myCommand = new SqlCommand(sql, conn);
conn.Open();
using (var reader = myCommand.ExecuteReader())
{
while (reader.Read())
{
SysColumn column = new SysColumn();
column.Name = reader.GetString(0);
column.ColOrder = reader.GetInt16(1);
columns.Add(column);
}
}
conn.Close();
}
return columns;
}
static Type GetUnderlyingType(Type type)
{
Type unType = Nullable.GetUnderlyingType(type); ;
if (unType == null)
unType = type;
return unType;
}
class SysColumn
{
public string Name { get; set; }
public int ColOrder { get; set; }
}
}
}
例子
#region SqlBulkCopy 批量插入
// 使用 SqlBulkCopy 进行批量插入
string myConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(myConnStr))
{
if (PlateSectionOQCList.Count > 0)
{
conn.BulkCopy(PlateSectionOQCList, 20000, "TB_QC_PlateForOQCReport");
}
}
#endregion
