有导出，就有导入，又时候还会有合并列导入.
下面展示一些 c#代码。
前台使用了 layui
<link href="~/Resources/layui/css/layui.css" rel="stylesheet" />
<script src="~/Resources/layui/layui.js"></script>
<script src="~/Resources/layui/layui.all.js"></script>
layui.use('upload', function () {
var upload = layui.upload;
//指定允许上传的文件类型
upload.render({
elem: '#wanchengDaoru'//被点击的按钮的id
, url: '/XXX/XXXXX'
, accept: "file" //文件，默认是图片,弹出的提示不同
, exts: 'xlsx|xls' //普通文件
//, before: function (obj) {
// //var orderID = $("#orderID").val();
// // this.data = { "jiance_id": checkJian_id, "shuxing": "" }//携带动态参数
//}
, done: function (data) {
var success = data.success;
var msg = data.msg;
var swalType = success ? 'success' : 'error';
var text = success ? '保存成功' : '保存失败';
swal({
title: "提示信息",
text: msg,
type: swalType,
showCancelButton: false,
confirmButtonColor: "#DD6B55",
confirmButtonText: "确定",
closeOnConfirm: true
}, function () {
//swal.close();
if (success) {
} else {
}
});
}
});
})
后台对应的写法
//导入excel文件信息
public ActionResult BingHaiImportExcel()
{
HttpFileCollectionBase file = Request.Files;//获取选中文件
if (file.Count > 0)
{
//文件重新命名
string _guid = DateTime.Now.ToString("yyyy年MM月dd日") + "-" + Guid.NewGuid().ToString().ToUpper().Replace("-", "");
string fileName = file[0].FileName;
Stream fs = file[0].InputStream;
if (file != null && file[0].ContentLength > 0)
{
using (StreamReader sr = new StreamReader(fs, Encoding.UTF8))
{
//文件保存本地路径
string save_diretion = Server.MapPath("../upload/");
string save_path = save_diretion + _guid + "-" + fileName;
try
{
file[0].SaveAs(save_path);
if (fileName.EndsWith(".xls") || fileName.EndsWith(".xlsx"))
{
string message = "";
message = ReadFromExcelFilewh(save_path);
if (message != "")
{
return Json(new
{
code = 0,
src = "",
name = "",
msg = "导入数据失败," + message,
success = false
}, JsonRequestBehavior.AllowGet);
}
}
}
catch (Exception e)
{
// System.IO.File.Delete(save_path);
return Json(new
{
code = 0,
src = "",
name = "",
msg = "导入数据失败," + e.Message,
success = false
}, JsonRequestBehavior.AllowGet);
throw;
}
}
}
}
return Json(new
{
code = 1,
src = "",
name = "",
msg = "导入数据成功",
success = true
}, JsonRequestBehavior.AllowGet);
}
public string ReadFromExcelFilewh(string save_path)
{
string message = "";
List<Parameter> insertSqlList = new List<Parameter>();
using (MySqlConnection conn = new MySqlConnection(MySqlStr))
{
conn.Open();
//开启事务
MySqlTransaction SqlTransaction = conn.BeginTransaction();
//将事务应用于Command
MySqlCommand cmd = new MySqlCommand();
// string PatrolTaskGuid = System.Guid.NewGuid().ToString("N");
cmd.Connection = conn;
cmd.Transaction = SqlTransaction;
try
{
//这里写了两种后缀对应的 workBook 的类型，
if (fileName.EndsWith(".xls"))
{
HSSFWorkbook workBook = new HSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
HSSFSheet sheet1 = (HSSFSheet)workBook.GetSheetAt(0);
}else{
XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
}
//后续统一的方法
#region sheet1 添加表
string plan_id = "";//主表id
int rowCount = sheet1.LastRowNum;
IRow row = sheet1.GetRow(0);
//去掉前三行
int ishebing = 0;
string qiaoliangming = "";
for (int a = 2; a <= sheet1.LastRowNum; a++)
{
// count = a;
row = sheet1.GetRow(a); //读取当前行数据
if (row != null)
{
#region 参数
string Systime = DateTime.Now.ToString();
string qiaoliangmingc = "";
//判断是否是合并项
if (isMergedRegion(sheet1, a, 1))
{
qiaoliangmingc = getMergedRegionValue(sheet1, a, 1);
if (qiaoliangmingc == qiaoliangming)
{
ishebing++;
}
else
{
qiaoliangming = qiaoliangmingc;
ishebing = 1;
}
}
else
{
ishebing = 1;
qiaoliangmingc = getCellValue(row.GetCell(1)).ToString();
}
string Sourceresult = "";
if (isMergedRegion(sheet1, a, 2))
{
Sourceresult = getMergedRegionValue(sheet1, a, 2);
}
else
{
Sourceresult = getCellValue(row.GetCell(2)).ToString();
}
string Diseases = isNullCell(row.GetCell(3)).ToString();
string DangersNum = isNullCell(row.GetCell(4)).ToString();
string Measures = isNullCell(row.GetCell(5)).ToString();
//获取时间
string Stardate = "";
ICell cellOrderDate = row.GetCell(6);
if (cellOrderDate != null)
{
if (cellOrderDate.CellType == CellType.Numeric)
{
if (DateUtil.IsCellDateFormatted(cellOrderDate))
{
Stardate = DateTime.FromOADate(cellOrderDate.NumericCellValue).ToString("yyyy-MM-dd");
}
else
{
Stardate = DateTime.FromOADate(cellOrderDate.NumericCellValue).ToString("yyyy-MM-dd");
}
}
else
{
Stardate = row.GetCell(6).ToString();
}
}
#endregion
}
else
{
return "";
}
}
dbHelperMySQL.ExecuteNonQueryTran(insertSqlList);
#endregion
cmd.Transaction.Commit();
}
catch (Exception e)
{
message = e.Message;
cmd.Transaction.Rollback();
}
}
return message;
}
```
```c#
// 一些用到的方法类
private bool isMergedRegion(XSSFSheet sheet, int row, int column)
{
int sheetMergeCount = sheet.NumMergedRegions;//.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++)
{
NPOI.SS.Util.CellRangeAddress range = sheet.GetMergedRegion(i);
int firstColumn = range.FirstColumn;// .getFirstColumn();
int lastColumn = range.LastColumn;//.getLastColumn();
int firstRow = range.FirstRow;//.getFirstRow();
int lastRow = range.LastRow;//getLastRow();
if (row >= firstRow && row <= lastRow)
{
if (column >= firstColumn && column <= lastColumn)
{
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public string getMergedRegionValue(XSSFSheet sheet, int row, int column)
{
int sheetMergeCount = sheet.NumMergedRegions;
for (int i = 0; i < sheetMergeCount; i++)
{
NPOI.SS.Util.CellRangeAddress ca = sheet.GetMergedRegion(i);
int firstColumn = ca.FirstColumn;// .getFirstColumn();
int lastColumn = ca.LastColumn;//.getLastColumn();
int firstRow = ca.FirstRow;//.getFirstRow();
int lastRow = ca.LastRow;//getLastRow();
if (row >= firstRow && row <= lastRow)
{
if (column >= firstColumn && column <= lastColumn)
{
IRow fRow = sheet.GetRow(firstRow);
ICell fCell = fRow.GetCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public string getCellValue(ICell cell)
{
string value = "";
if (cell != null)
{ //Numeric 类型
if (cell.CellType == CellType.Numeric)
{
double value2 = cell.NumericCellValue;
value = value2 * 100 + "%";
return value;
}
else if (cell.CellType == CellType.String)
{
value = cell.StringCellValue;
}
}
else
{
value = "";
}
return value;
}
2986
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
