c# excel文档导入及excel存储数据库

1.前端代码

本例使用初始MVC代码结构讲解excel文档的上传及保存至数据库操作
下面为前端代码
实现效果如图

<form id="formfile" enctype="multipart/form-data" style="float:left">

<span id="spFileUpload">

<button type="button" id="btnxuanze" class="btn btn-xs btn-info" οnclick="uploadClick()"><i class=""></i>选择文件</button>

@Html.TextBox("f_fileName", "", new { @class = "input-middle" })
<input type="file" name="fileField" class="files" id="fileField" style="opacity:0;display:none" size="28" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />
<button type="button" class="btn btn-xs btn-success" οnclick="uploadfile()">上传</button>
</span>
</form>

2.后台代码

为了方便演示,设计excel格式数据,如下图
同时设计数据库,与excel对应字段 (以上均为方便演示)
注意表列名与字段名转换对应关系

c# excel文档导入及excel存储数据库

--数据库建表脚本

use CourseManageDB --修改成自己本地数据库
go

if exists (select * from sysobjects where name= 't_externalPay')
drop table t_externalPay
go

create table t_externalPay
(
   f_bankCode varchar(50),
   f_province varchar(50),
   f_city varchar(50),
   f_bankOther varchar(50),
   f_remittanceAccount varchar(50),
   f_adminName varchar(50),
   f_money decimal(8,2),
   f_order int
)
go

//后台程序处理上传excel并存至sqlserver代码

public ActionResult ExcelToDataTable()
{
IWorkbook workbook = null;
ISheet sheet = null;
DataTable table = new DataTable();
var fileData = Request.Files["myfirstField"];
workbook = WorkbookFactory.Create(fileData.InputStream);
//文件名称,带时间,方便日后导出数据时,根据条件查询
var fileName = fileData.FileName.ToString().Split('.')[0] + " " + DateTime.Now.ToString("HHmmss") + ".xls";

var cellstyle = workbook.CreateCellStyle();
var hssfDataFormat = workbook.CreateDataFormat();
cellstyle.DataFormat = hssfDataFormat.GetFormat("@");

Dictionary<string, string> dictionary = new Dictionary<string, string>();
dictionary.Add("银行", "f_bankCode");
dictionary.Add("省份", "f_province");
dictionary.Add("城市", "f_city");
dictionary.Add("分行", "f_bankOther");
dictionary.Add("账号", "f_remittanceAccount");
dictionary.Add("姓名", "f_adminName");
dictionary.Add("金额", "f_money");
dictionary.Add("人员代号", "f_order");

if (workbook != null)
{
sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum + 1;//总行数=内容总数+列头
IRow headerRow = sheet.GetRow(0); //首行
int cellCount = headerRow.LastCellNum; //一行最后一个方格的编号为总列数
//表头数据
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(dictionary.Where(w=>w.Key == headerRow.GetCell(i).StringCellValue).Select(w=>w.Value).FirstOrDefault());
table.Columns.Add(column);
}
//读取列数据
for (int i = 1; i < rowCount; i++)
{
IRow rows = sheet.GetRow(i);
if (rows != null && rows.GetCell(0) != null && rows.GetCell(0).StringCellValue != null)
{
//创建与表格相同结构的新行
DataRow datarow = table.NewRow();
for (int j = rows.FirstCellNum; j < cellCount; j++)
{
if (rows.GetCell(j) != null && rows.GetCell(j).ToString() != "")
{
//给表每行数据赋值
datarow[j] = rows.GetCell(j).ToString();
}
}
table.Rows.Add(datarow);
}
}
}

//连接数据库,准备写入
string conn = ConfigurationManager.ConnectionStrings["conn_mydb"].ToString();
SqlConnection connection = new SqlConnection(conn);

if (connection.State == ConnectionState.Closed)
{
connection.Open();
}

using (SqlTransaction transaction = (SqlTransaction)connection.BeginTransaction())
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction))
{
sqlBulkCopy.DestinationTableName = "t_externalPay";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
}
sqlBulkCopy.BatchSize = table.Rows.Count;
try
{
sqlBulkCopy.WriteToServer(table);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}

return View("Contact");
}

c# excel文档导入及excel存储数据库

3.总结

初学编程万事开头难,只要用心你也可以.

c# excel文档导入及excel存储数据库

1.前端代码

2.后台代码

3.总结

相关推荐