using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace BysyImportExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 获取指定路径、指定工作簿名称的Excel数据
/// </summary>
/// <param name="FilePath">文件存储路径</param>
/// <param name="WorkSheetName">工作簿名称</param>
/// <returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns>
public DataTable GetExcelData(string FilePath, string WorkSheetName)
{
DataTable dtExcel = new DataTable();
OleDbConnection con = new OleDbConnection(GetExcelConnection(FilePath));
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + WorkSheetName+"]", con);
//读取
con.Open();
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
con.Close();
dtExcel.TableName = WorkSheetName;
//返回
return dtExcel;
}
/// <summary>
/// 获取链接字符串
/// </summary>
/// <param name="strFilePath"></param>
/// <returns></returns>
public string GetExcelConnection(string strFilePath)
{
if (!File.Exists(strFilePath))
{
throw new Exception("指定的Excel文件不存在！");
}
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended properties=\"Excel 8.0;Imex=1;HDR=Yes;\"";
//@"Provider=Microsoft.Jet.OLEDB.4.0;" +
//@"Data Source=" + strFilePath + ";" +
//@"Extended Properties=" + Convert.ToChar(34).ToString() +
//@"Excel 8.0;" + "Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
}
/// <summary>
/// 读取工作薄
/// </summary>
/// <returns></returns>
public List<string> getGZB(string path)
{
List<string> lis = new List<string>();
OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;imex=1;hdr=Yes\";");
objConn.Open();
System.Data.DataTable dt = null;
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
//Response.Write(row["TABLE_NAME"].ToString());
lis.Add(row["TABLE_NAME"].ToString());
i++;
}
if (objConn != null)
{
objConn.Close(); objConn.Dispose();
} if (dt != null)
{
dt.Dispose();
}
return lis;
}
/// <summary>
/// 选择文件开始导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
List<int> lisids = new List<int>();
try
{
button1.Text = "正在执行，请稍后！";
button1.Enabled = false;
OpenFileDialog fileDialog = new OpenFileDialog();
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//获取用户选择文件的后缀名
string extension = Path.GetExtension(fileDialog.FileName);
//声明允许的后缀名
string[] str = new string[] { ".xls" };
if (!str.Contains(extension))
{
MessageBox.Show("友情提示！为了支持更广我们选择了Office2003版本的excel作为模板，如果您是高版本请另存为office2003");
}
else
{
List<string> lisgzb = getGZB(fileDialog.FileName);//获取工作薄
foreach (var a in lisgzb)
{//循环工作薄
var i = 0;
var dt2 = GetExcelData(fileDialog.FileName, a.Replace('\'',' ').Trim()+"A:M");
//foreach (DataRow row in dt2.Rows.)
//{
for (int j = dt2.Rows.Count-1; j >=0; j--)
{
var row = dt2.Rows[j];
i++;
if (string.IsNullOrEmpty(Convert.ToString(row["设备编号"])) && string.IsNullOrEmpty(Convert.ToString(row["名称"])))
{
continue;
}
Woo.Model.WOO_ASSET_MANAGMENT info = new Woo.Model.WOO_ASSET_MANAGMENT();
info.NO = Convert.ToString(row["设备编号"]);//设备编号
info.NAME = Convert.ToString(row["名称"]);//名称
if (radioButton1.Checked)
{
info.POST_TYPE = 0;//医工处
}
else
{
info.POST_TYPE = 1;//总务处
}
info.FACTORY = Convert.ToString(row["厂家"]);//厂家
info.MODEL = Convert.ToString(row["型号"]);//厂家
try
{
info.UNIT_PRICE = Convert.ToDecimal(row["单价"]);
}
catch (Exception ex1)
{
string strmsg = "excel【"+a.ToString()+"】倒序第" + i + "行，设备编号是：" + info.NO + ",名称是：" + info.NAME + "。单价列数据有问题";
Woo.Utility.LogUtility.WriteMsgLog(strmsg);
Woo.Utility.LogUtility.WriteErrorLog(ex1);
info.UNIT_PRICE = null;
}
try
{
info.BUY_TIME = Convert.ToDateTime(row["购置日期"]);
}
catch (Exception ex2)
{
string strmsg = "excel【"+a.ToString()+"】倒序第" + i + "行，设备编号是：" + info.NO + ",名称是：" + info.NAME + "。购置日期列列数据有问题";
Woo.Utility.LogUtility.WriteMsgLog(strmsg);
Woo.Utility.LogUtility.WriteErrorLog(ex2);
info.BUY_TIME = null;
}
info.LOCATION = Convert.ToString(row["存放地点"]);
info.YEAR_USER_TIME = Convert.ToString(row["年使用时数"]);
info.REAMRK = Convert.ToString(row["备注"]);
info.CREATE_DATETIME = System.DateTime.Now;
info.MODIFY_DATETIME = System.DateTime.Now;
info.MODIFY_USER_ID = 1;
info.CREATE_USER_ID = 1;
try
{
var cainfo = Woo.BLL.WooSetting.Category.GetInfoByNameAndType(a.Replace('$', ' ').Trim(), "AssetStatus");
if (cainfo != null)
{
info.STATUS = cainfo.ID;
}
else
{
var cainfo2 = Woo.BLL.WooSetting.Category.GetInfoByNameAndType("在用", "AssetStatus");
if (cainfo2 == null)
{
info.STATUS = null;
}
else
{
info.STATUS = cainfo2.ID;
}
//info.STATUS = cainfo2 == null ? -2 : cainfo2.ID;
}
}
catch (Exception)
{
info.STATUS = null;
}
try
{
var pritstirng = Convert.ToString(row["属性"]);
//if (string.IsNullOrEmpty(pritstirng))
//{
// var cainfo3 = Woo.BLL.WooSetting.Category.GetInfoByNameAndType(a.Replace('$', ' ').Trim(), "AssetPrpperty");
// if (cainfo3 != null)
// {
// info.PRPPERTY = cainfo3.ID;
// }
// else
// {
// info.PRPPERTY = null;
// }
//}
//else
//{
var cainfo4 = Woo.BLL.WooSetting.Category.GetInfoByNameAndType(pritstirng, "AssetPrpperty");
if (cainfo4 == null)
{
info.PRPPERTY = null;
}
else
{
info.PRPPERTY = cainfo4.ID;
}
// }
}
catch (Exception)
{
info.PRPPERTY = null;
}
var res = Woo.BLL.WooAsset.AssetManager.CheckONe(info);
if (!res)
{
Woo.BLL.WooAsset.AssetManager.Insert(info);
//StringBuilder str2 = new StringBuilder("insert into WOO_ASSET_MANAGMENT(NO,NAME,POST_TYPE,FACTORY,MODEL,UNIT_PRICE,BUY_TIME,LOCATION,YEAR_USER_TIME,REAMRK,CREATE_DATETIME,MODIFY_DATETIME,MODIFY_USER_ID,CREATE_USER_ID,STATUS,PRPPERTY)");
//str2.Append(" values(");
//str2.Append("'" + info.NO + "',");
//str2.Append("'" + info.NAME + "',");
//str2.Append(info.POST_TYPE + ",");
//str2.Append("'" + info.FACTORY + "',");
//str2.Append("'" + info.MODEL + "',");
//if (info.UNIT_PRICE == null)
//{
// str2.Append(0 + ",");
//}
//else
//{
// str2.Append(info.UNIT_PRICE + ",");
//}
//str2.Append("'" + info.BUY_TIME + "',");
//str2.Append("'" + info.LOCATION + "',");
//str2.Append("'" + info.YEAR_USER_TIME + "',");
//str2.Append("'" + info.REAMRK + "',");
//str2.Append("'" + info.CREATE_DATETIME + "',");
//str2.Append("'" + info.MODIFY_DATETIME + "',");
//str2.Append(info.MODIFY_USER_ID + ",");
//str2.Append(info.CREATE_USER_ID + ",");
//if (info.STATUS == null)
//{
// str2.Append(-1 + ",");
//}
//else
//{
// str2.Append(info.STATUS + ",");
//}
//if (info.PRPPERTY == null)
//{
// str2.Append(-1);
//}
//else
//{
// str2.Append(info.PRPPERTY);
//}
//str2.Append(")");
//Woo.Utility.SqlHelper.ExecuteNonQuery(Woo.Utility.Constant.CONNECTION_STRING, CommandType.Text, str2.ToString(), null);
}
lisids.Add(info.ID);
}
}
MessageBox.Show("导入程序执行完毕");
button1.Text = "选择文件";
button1.Enabled = true;
}
}
}
catch (Exception ex)
{
//Woo.BLL.WooAsset.AssetManager.DeleBLock(lisids);//如果出错了就全部删除
MessageBox.Show(ex.Message);
Woo.Utility.LogUtility.WriteErrorLog(ex);
}
}
/// <summary>
/// 替换逗号
/// </summary>
/// <param name="str1"></param>
/// <returns></returns>
private string Reps(string str1)
{
Regex re = new Regex(@"[,，]+", RegexOptions.None);
return re.Replace(str1, " ");
}
}
}
联系客服
