private void insertInToDB(Jewelry je)
{
string connectionFormat = @"Data Source=C:\xhz\{0};";
//更新数据，不管是编辑，还是新增
string connectString = string.Format(connectionFormat, "first");
//把图片转换成base64编码
string imageString = helper.ImageToBase64(je.Image);
string sqlAll, sql;
if (true)
{
sqlAll = "insert into Data (guid,image,buytime,buyprice,buywho,goldprice,type,color,mark,buySource,ownwho,state,borrowtime,borrowwho,borrowprice,borrowreturntime,saletime,salewho,saleprice,salestate,createtime,updatetime) Values('{0}','{1}','{2}',{3},'{4}',{5},'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',{14},'{15}','{16}','{17}',{18},'{19}','{20}','{21}')";
sql = string.Format(sqlAll, je.Guid, imageString, Convert.ToDateTime(je.BuyTime).ToString("s"), je.BuyPrice, "", je.GoldPrice, je.Type, je.Color, je.Mark, je.BuySource, "", je.State, Convert.ToDateTime(null).ToString("s"), "", 0, Convert.ToDateTime(null).ToString("s"), Convert.ToDateTime(je.SaleTime).ToString("s"), je.SaleWho, je.SalePirce, "", System.DateTime.Now.ToString("s"), System.DateTime.Now.ToString("s"));
}
//else
//{
// sqlAll = "update Data set image = '{0}',buytime = '{1}',buyprice = '{2}',buywho = '{3}',goldprice = {4},type = '{5}',color = '{6}',mark = '{7}',buySource = '{8}',ownwho = '{9}',state = '{10}',borrowtime = '{11}',borrowwho = '{12}',borrowprice = {13},borrowreturntime = '{14}',saletime = '{15}',salewho = '{16}',saleprice = {17},salestate = '{18}',updatetime = '{19}' where guid='{20}'";
// sql = string.Format(sqlAll, imageString, Convert.ToDateTime(je.BuyTime).ToString("s"), je.BuyPrice, je.BuyWho, je.GoldPrice, je.Type, je.Color, je.Mark, je.BuySource, je.OwnWho, je.State, Convert.ToDateTime(je.BorrowTime).ToString("s"), je.BorrowWho, je.BorrowPirce, Convert.ToDateTime(je.BorrowReturnTime).ToString("s"), Convert.ToDateTime(je.SaleTime).ToString("s"), je.SaleWho, je.SalePirce, je.SaleState, System.DateTime.Now.ToString("s"), je.Guid);
//}
int temp;
using (SQLiteConnection sqlcon = new SQLiteConnection(connectString))
{
SQLiteCommand cmd = new SQLiteCommand(sql, sqlcon);
sqlcon.Open();
temp = cmd.ExecuteNonQuery();
sqlcon.Close();
}
}
public void table()
{
try
{
string filePath = @"C:\xhz\综合.xlsx";
//根据Excel流转换为spreadDocument对象
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))//Excel文档包
{
//Workbook workBook = spreadDocument.WorkbookPart.Workbook;//主文档部件的根元素
//Sheets sheeets = workBook.Sheets;//块级结构（如工作表、文件版本等）的容器
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
//获取Excel中SheetName集合
List<string> sheetNames = GetSheetNames(workBookPart);
//遍历每个sheet页
foreach (string sheetName in sheetNames)
{
if (!sheetName.Equals("2013"))
{
break;
}
IEnumerable<Row> sheetRows = GetWorkBookPartRows(workBookPart, sheetName);
if (sheetRows == null || sheetRows.Count() <= 0)
{
return;
}
//将数据导入DataTable,假定第一行为列名,第二行以后为数据
foreach (Row row in sheetRows)
{
//获取Excel中的列头
if (row.RowIndex == 1)
{
}
else
{
//Excel第二行同时为DataTable的第一行数据
//DataRow dataRow = GetDataRow(row, dataTable, workBookPart);
//if (dataRow != null)
//{
// dataTable.Rows.Add(dataRow);
//}
IEnumerable<Cell> cells = row.Elements<Cell>();
Jewelry je = new Jewelry(true);
string year = string.Empty;
string month = string.Empty;
foreach (Cell cell in cells)
{
je.Row = cell.CellReference.Value.Substring(1);
je.Col = cell.CellReference.Value.Substring(0, 1);
string cellVlue = GetCellValue(cell, workBookPart);
switch (je.Col)
{
case "A":
year = cellVlue;
break;
case "B":
month = cellVlue;
if (string.IsNullOrEmpty(month) || string.IsNullOrEmpty(year))
{
}
else
{
DateTime dt = new DateTime(int.Parse(year), int.Parse(month), 1);
je.BuyTime = dt.ToString("s");
}
break;
case "C":
break;
case "D":
break;
case "E":
if (string.IsNullOrEmpty(cellVlue) || string.IsNullOrWhiteSpace(cellVlue))
{
}
else
{
string[] temp = cellVlue.Split('.');
if (temp.Count() > 1)
{
int last = int.Parse(temp[1].Substring(0, 1));
if (last > 4)
{
int temp1 = int.Parse(temp[0])+1;
je.GoldPrice = Double.Parse(temp1.ToString());
}
else
{
int temp2 = int.Parse(temp[0]);
je.GoldPrice = Double.Parse(temp2.ToString());
}
}
else if (temp.Count() == 1)
{
int temp3 = int.Parse(temp[0]);
je.GoldPrice = Double.Parse(temp3.ToString());
}
}
break;
case "F":
je.Type = cellVlue;
break;
case "G":
je.Color = cellVlue;
break;
case "H":
if (cellVlue.Equals("是"))
{
je.State = "卖出";
}
else
{
je.State = "未卖";
}
break;
case "I":
je.SaleWho = cellVlue;
break;
case "J":
if (cellVlue.Length == 8)
{
DateTime dt1 = new DateTime(int.Parse(cellVlue.Substring(0, 4)), int.Parse(cellVlue.Substring(4, 2)), int.Parse(cellVlue.Substring(6, 2)));
je.SaleTime = dt1.ToString("s");
}
else if (cellVlue.Length == 4)
{
int temp = 0;
if (int.TryParse(cellVlue, out temp))
{
DateTime dt2 = new DateTime(int.Parse(cellVlue.Substring(0, 4)), 1, 1);
je.SaleTime = dt2.ToString("s");
}
}
break;
case "K":
if (string.IsNullOrEmpty(cellVlue) || string.IsNullOrWhiteSpace(cellVlue))
{
}
else
{
je.Mark = cellVlue;
}
break;
case "L":
if (string.IsNullOrEmpty(cellVlue) || string.IsNullOrWhiteSpace(cellVlue))
{
}
else
{
je.BuySource = cellVlue;
}
break;
default:
break;
}
}
oje.Add(je);
}
}
string s = "";
}
}
}
catch (Exception exp)
{
//throw new Exception("可能Excel正在打开中,请关闭重新操作！");
}
}
