前端JS
//bootstrap 分页
$('#data_table').DataTable({
searching: false,
destroy: true,
autoWidth: true,
ordering: false,
});
//按钮全选和反选
$('#check_all').click(function() {
if (this.checked) {
$("input[name='CheckBoxName']").each(function () {
this.checked = true;
});
}
else {
$("input[name='CheckBoxName']").each(function () {
this.checked = false;
});
}
});
//批量写入数据库
$('#uploadDatabase').click(function () {
var ids = new Array();
$("input[name='CheckBoxName']").each(function () {
if (this.checked) {
ids.push($(this).val());
}
});
console.log(ids);
if (ids.length == 0) {
alert('请至少选择一条数据！');
return;
}
$.ajax({
url: '/ICPMS/UploadDatabase',
type: 'post',
data: { ids: ids },
success: function () {
window.location.reload();
},
error: function () {
}
});
控制器Code
public void UploadDatabase(string[] ids)
{
for (int i = 0; i < ids.Length; i++)
{
/*using (BaseDBContext db = new BaseDBContext())
{
FileUploadRecord find = db.FileUploadRecords.Find(ids[i]);
find.Status = 3;//上传至数据库
db.FileUploadRecords.Attach(find);
db.Entry<FileUploadRecord>(find).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}*/
//获取服务器地址
string path = System.Web.HttpContext.Current.Server.MapPath(@"~\\UploadFile\\" + ids[i]);
string str;
DataSet ds = NPOIHelper.ExcelToDataSet(path, out str, ids[i]);
string con = "Data Source = 127.0.0.1\\MSSQLSERVER2018; Initial Catalog = xxx; Persist Security Info = True; User ID = sa; Password = xxx";
using (SqlBulkCopy sbc = new SqlBulkCopy(con, SqlBulkCopyOptions.Default))
{
sbc.DestinationTableName = "[xxx].[dbo].[xxx]";
sbc.BulkCopyTimeout = 10;
sbc.WriteToServer(ds.Tables[0]);
}
}
}
NPOI插件内容需修改
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
namespace ExcelHelper
{
class NPOIHelper
{
/// <summary>
/// Excel转换成DataTable（.xls）
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string filePath)
{
var dt = new DataTable();
using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var hssfworkbook = new HSSFWorkbook(file);
var sheet = hssfworkbook.GetSheetAt(0);
for (var j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
var rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
var row = (HSSFRow)rows.Current;
var dr = dt.NewRow();
for (var i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
switch (cell.CellType)
{
case CellType.Blank:
dr[i] = "[null]";
break;
case CellType.Boolean:
dr[i] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dr[i] = cell.ToString();
break;
case CellType.String:
dr[i] = cell.StringCellValue;
break;
case CellType.Error:
dr[i] = cell.ErrorCellValue;
break;
case CellType.Formula:
try
{
dr[i] = cell.NumericCellValue;
}
catch
{
dr[i] = cell.StringCellValue;
}
break;
default:
dr[i] = "=" + cell.CellFormula;
break;
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// Excel转换成DataSet（.xlsx/.xls）
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string filePath, out string strMsg, string uuid)
{
strMsg = "";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
string fileName = Path.GetFileName(filePath).ToLower();
try
{
ISheet sheet = null;
int sheetNumber = 0;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (fileType == ".xlsx")
{
// 2007版本
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;//Sheet 数量
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);//获取sheet名称
sheet = workbook.GetSheet(sheetName); //获取sheet内容
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
//加入一列id
dt.Columns.Add("Id", typeof(Int32)).SetOrdinal(0);
foreach (DataRow dr in dt.Rows)
{
dr["Id"] = 1;
}
//加入一列Chemical
dt.Columns.Add("Chemical", typeof(String)).SetOrdinal(2);
string chemical = sheet.GetRow(0).GetCell(0).ToString().ToUpper();
foreach (DataRow dr in dt.Rows)
{
dr["Chemical"] = chemical;
}
//删除datatable表格第一列
dt.Columns.RemoveAt(1);
//加入一列Fileid
dt.Columns.Add("FileId", typeof(String));
foreach (DataRow dr in dt.Rows)
{
dr["FileId"] = uuid;
}
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
;
}
}
}
}
else if (fileType == ".xls")
{
// 2003版本
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
//加入一列id
dt.Columns.Add("Id", typeof(Int32)).SetOrdinal(0);
foreach (DataRow dr in dt.Rows)
{
dr["Id"] = 1;
}
//加入一列Chemical
dt.Columns.Add("Chemical", typeof(String)).SetOrdinal(2);
string chemical = sheet.GetRow(0).GetCell(0).ToString().ToUpper();
foreach (DataRow dr in dt.Rows)
{
dr["Chemical"] = chemical;
}
//删除datatable表格第一列
dt.Columns.RemoveAt(1);
//加入一列Fileid
dt.Columns.Add("FileId", typeof(String));
foreach (DataRow dr in dt.Rows)
{
dr["FileId"] = uuid;
}
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
;
}
}
}
}
return ds;
}
catch (Exception ex)
{
strMsg = ex.Message;
return null;
}
}
/// <summary>
/// 获取sheet表对应的DataTable
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="strMsg"></param>
/// <returns></returns>
private static DataTable GetSheetDataTable(ISheet sheet, out string strMsg )
{
strMsg = "";
DataTable dt = new DataTable();
string sheetName = sheet.SheetName;
int startIndex = 0;// sheet.FirstRowNum; //设置读取开始行
int lastIndex = sheet.LastRowNum;//获取最后一行
//最大行数
int cellCount = 0;
IRow maxRow = sheet.GetRow(0); //for循环意义没有
for (int i = startIndex; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);//遍历i行信息
if (row != null && cellCount < row.LastCellNum)
{
cellCount = row.LastCellNum;//获取最大行数
maxRow = row;
}
}
//列名设置
try
{
for (int i = 0; i < 23; i++)//maxRow.FirstCellNum maxRow.LastCellNum
{
dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
//DataColumn column = new DataColumn("Column" + (i + 1).ToString());
//dt.Columns.Add(column);
}
}
catch
{
strMsg = "工作表" + sheetName + "中无数据";
return null;
}
//数据填充
for (int i = 7; i <= lastIndex; i++) //遍历行数
{
IRow row = sheet.GetRow(i);
DataRow drNew = dt.NewRow();
if (row != null)
{
//判断重复 关键字UPDATE
String flag = "";
ICell Cell = row.GetCell(23);
if (Cell != null)
{
flag = Cell.StringCellValue.ToString();
}
if (flag.ToUpper().Equals("UPDATE") && flag != null)
{
//row.FirstCellNum
for (int j = 0; j < 23; j++) //遍历列数
{
if (row.GetCell(j) != null)
{
ICell cell = row.GetCell(j); //取得第i个值
switch (cell.CellType)
{
case CellType.Blank:
//drNew[j] = string.Empty;
break;
case CellType.Numeric:
if (cell.NumericCellValue.ToString().Contains("/") && (cell.CellStyle.DataFormat == 176) || (cell.CellStyle.DataFormat == 177) || (cell.CellStyle.DataFormat == 188))
{
drNew[j] = cell.DateCellValue;
}
else if (cell.NumericCellValue.ToString().Contains("."))
{
drNew[j] = (float)cell.NumericCellValue;
}
else
{
drNew[j] = (int)cell.NumericCellValue;
}
break;
case CellType.String:
drNew[j] = cell.StringCellValue.ToUpper();
break;
case CellType.Formula:
try
{
drNew[j] = cell.NumericCellValue;
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
drNew[j] = cell.NumericCellValue.ToString("#0.00");
}
catch
{
try
{
drNew[j] = cell.StringCellValue;
}
catch { }
}
break;
default:
drNew[j] = cell.StringCellValue;
break;
}
}
}
}
else { continue; }
}
dt.Rows.Add(drNew);
}
return dt;
}
}
}
实体类
namespace iLab.Dao.Entity
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
//[Table("ICPMS_CHEMICAL_DATA_II")]
public partial class ICPMS_CHEMICAL_DATA_II
{
//[Key]
public long Id { get; set; }
public string Chemical { get; set; }
public string Course { get; set; }
public Nullable<System.DateTime> SampleDate { get; set; }
public string System { get; set; }
public string Sampling_goal { get; set; }
public string Location { get; set; }
public Nullable<System.DateTime> UploadDate { get; set; }
public Nullable<double> Na { get; set; }
public Nullable<double> Mg { get; set; }
public Nullable<double> Al { get; set; }
public Nullable<double> K { get; set; }
public Nullable<double> Ca { get; set; }
public Nullable<double> Cr { get; set; }
public Nullable<double> Fe { get; set; }
public Nullable<double> Co { get; set; }
public Nullable<double> Ni { get; set; }
public Nullable<double> Cu { get; set; }
public Nullable<double> Zn { get; set; }
public Nullable<double> Ag { get; set; }
public Nullable<double> Pb { get; set; }
public Nullable<double> Total { get; set; }
public string Status { get; set; }
public string Remark { get; set; }
public string FileId { get; set; }
public Nullable<int> Tag_Id { get; set; }
}
}
excel读取格式
1216
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
