using Community.DAL;
|
using Community.Model.Build;
|
//using NPOI.CS;
|
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.Forms;
|
|
namespace Community.Excel.Common
|
{
|
public class XlsTools
|
{
|
//public static readonly ExcelHelper _excel = new ExcelHelper();
|
|
/// <summary>
|
/// 读取文件至字节数组
|
/// </summary>
|
/// <param name="fileName">文件名</param>
|
/// <returns>字节数组</returns>
|
public static byte[] FileToBytes(string fileName)
|
{
|
byte[] bytes = null;
|
FileStream fs = null;
|
|
try
|
{
|
FileInfo file = new FileInfo(fileName);
|
if (file != null)
|
{
|
fs = file.OpenRead();
|
bytes = new Byte[fs.Length];
|
fs.Read(bytes, 0, Convert.ToInt32(fs.Length));
|
fs.Flush();
|
}
|
}
|
catch
|
{
|
bytes = null;
|
}
|
finally
|
{
|
if (fs != null)
|
{
|
fs.Close();
|
}
|
}
|
|
return bytes;
|
}
|
|
//public static DataTable ReadXls(string xls)
|
//{
|
// if (string.IsNullOrEmpty(xls) || !File.Exists(xls))
|
// {
|
// MessageBox.Show("Excel文件不存在，请重新选择！");
|
// return null;
|
// }
|
|
// byte[] bytes = XlsTools.FileToBytes(xls);
|
// if (null == bytes || bytes.Length == 0)
|
// {
|
// MessageBox.Show("无法读取Excel文件！");
|
// return null;
|
// }
|
|
// return _excel.ExcelToTable(bytes);
|
//}
|
|
public static DataTable ReadXls(string xls, int sheet)
|
{
|
if (string.IsNullOrEmpty(xls) || !File.Exists(xls))
|
{
|
MessageBox.Show("Excel文件不存在，请重新选择！");
|
return null;
|
}
|
|
return ReadExcel.ReadXls(xls, sheet - 1);
|
}
|
|
public static List<House> ProcessData(string[] splits, string name, DataTable dt)
|
{
|
int bid = HouseDAL.GetBuildingId(name);
|
if (bid == -1)
|
{
|
MessageBox.Show("没有读取到楼宇Id！");
|
return null;
|
}
|
|
House h = null;
|
List<House> list = new List<House>();
|
for (int i = 0, c = dt.Rows.Count; i < c; i++)
|
{
|
DataRow dr = dt.Rows[i];
|
|
string title = GetStr(dr, 1);
|
if (title.IndexOf("房屋地址") > -1)
|
{
|
h = new House(bid);
|
SetHouse(i + 1, h, dr, 2, splits);
|
list.Add(h);
|
}
|
|
if (title.IndexOf("房屋状态") > -1)
|
{
|
SetStatus(h, dr, 2);
|
}
|
|
if (title.IndexOf("房主信息") > -1)
|
{
|
SetHousehold(h, dr, 1);
|
}
|
if (title.IndexOf("房主家庭成员") > -1)
|
{
|
SetHousehold(h, dr, 2);
|
}
|
if (title.IndexOf("承租人") > -1)
|
{
|
SetHousehold(h, dr, 3);
|
}
|
}
|
|
return list;
|
}
|
|
public static string GetStr(DataRow dr, int col)
|
{
|
if (col >= dr.Table.Columns.Count) return string.Empty;
|
|
object obj = dr[col];
|
if (obj == null || obj == DBNull.Value)
|
{
|
return string.Empty;
|
}
|
|
return obj.ToString().Trim();
|
}
|
|
public static void SetHouse(int i, House h, DataRow dr, int col, string[] splits)
|
{
|
h.FullName = GetStr(dr, col);
|
|
try
|
{
|
string[] strs = h.FullName.Split(splits, StringSplitOptions.RemoveEmptyEntries);
|
|
h.BuildNum = strs[0];
|
if (strs.Length > 1) h.Unit = strs[1];
|
if (strs.Length > 2) h.HouseNum = strs[2];
|
}
|
catch //(Exception ex)
|
{
|
MessageBox.Show("行号：" + i + "\r\n" + h.FullName + "：门牌号异常！");
|
}
|
}
|
|
// 自住\出租\闲置
|
public static void SetStatus(House h, DataRow dr, int col)
|
{
|
string str = GetStr(dr, col);
|
if (str.Length == 0)
|
{
|
h.HouseState = 1;
|
return;
|
}
|
|
string status = str.Substring(0, 2);
|
switch (status)
|
{
|
case "自住":
|
h.HouseState = 1;
|
break;
|
case "出租":
|
h.HouseState = 2;
|
break;
|
case "闲置":
|
h.HouseState = 3;
|
break;
|
}
|
//h.HouseStatus = str.Substring(0, 2);
|
h.Remark = str.Replace(status, "").Replace("（", "").Replace("）", "").Trim();
|
}
|
|
public static void SetHousehold(House h, DataRow dr, int houseType)
|
{
|
string name = GetStr(dr, 2);
|
if (string.IsNullOrEmpty(name)) return;
|
|
Household hh = new Household();
|
hh.Name = name;
|
hh.HoldType = houseType;
|
hh.Gender = GetStr(dr, 3);
|
hh.Nation = GetStr(dr, 4);
|
hh.Political = GetStr(dr, 5);
|
hh.IdNum = GetStr(dr, 6).ToUpper();
|
hh.Addr = GetStr(dr, 7);
|
hh.Phone = GetStr(dr, 8);
|
hh.Remark = GetStr(dr, 9);
|
|
h.Holds.Add(hh);
|
}
|
|
public static int InsertHouse(List<House> list)
|
{
|
try
|
{
|
int rs = 0;
|
foreach (House h in list)
|
{
|
h.Id = HouseDAL.GetHouseMaxId();
|
int count = HouseDAL.InsertHouse(h);
|
if (count == 0) continue;
|
|
foreach (Household hh in h.Holds)
|
{
|
hh.House = h.Id;
|
count = HouseDAL.InsertHousehold(hh);
|
}
|
|
rs++;
|
}
|
|
return rs;
|
}
|
catch (Exception ex)
|
{
|
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
|
}
|
|
return 0;
|
}
|
}
|
}
|
