private void LoadData()
{
if (chkZero.Checked == false)
{
if (string.IsNullOrEmpty(ClsSystem.gnvl(this.checkedWareHouse.EditValue, "")))
{
if (GlobalParameters.iLanugage > 10)
{
MessageBox.Show("Warehouse is not empty！");
}
else
{
MessageBox.Show("仓库信息不能为空！");
}
return;
}
}
string sql = @" select CONVERT(decimal(18,2),isnull(latestCost,0)) as latestCost, isnull(ware.code,'') as wareCode,isnull(inv.specification,'')as specification, isnull(ware.name,'') as wareName,
isnull(inv.code,'') as invCode, isnull(inv.name,'') as invName,isnull(unit.name,'') as unitName,convert(decimal(36,2),isnull(st.baseQuantity,0)) as qty,img.FileContent,cast(round(isnull(price.retailPrice,0),2) as numeric(20,2)) as retailPrice,
isnull(inv.priuserdefnvc1,'') as ItemName,
isnull(inv.priuserdefnvc2,'') as Materials,
isnull(inv.priuserdefnvc3,'') as Color,
isnull(inv.priuserdefnvc4,'') as Dimension,
isnull(inv.priuserdefnvc5,'') as COO,
cast(round(isnull(purchaseArrivalBaseQuantity,0),2) as numeric(20,2)) as r,
cast(round(isnull(saleDeliveryBaseQuantity,0),2) as numeric(20,2)) as c,
cast(round(isnull(st.baseQuantity,0)+isnull(purchaseArrivalBaseQuantity,0)-isnull(saleDeliveryBaseQuantity,0),2) as numeric(20,2)) as k
from AA_inventory as inv
left join ST_CurrentStock as st on st.idinventory= inv.id
left join AA_Warehouse as ware on st.idwarehouse=ware.id
left join AA_Unit as unit on unit.id= st.idbaseunit
left join eap_UserImageInfo as img on img.FileName = inv.imageFile
left join aa_inventoryclass as class on class.id=idinventoryclass
left join AA_InventoryPrice as price on price.idinventory = inv.id
where 1=1 " + StrWhere();
string sort = string.Empty;
if (!string.IsNullOrEmpty(this.checkedComBoxCol.EditValue.ToString()))
{
if (checkedComBoxCol.EditValue.ToString().Contains(","))
{
string[] depart = checkedComBoxCol.EditValue.ToString().Split(',');
string departmentCode = string.Empty;
for (int i = 0; i < depart.Length; i++)
{
departmentCode = departmentCode + "" + depart[i].ToString().Trim() + ",";
}
departmentCode = departmentCode.Substring(0, departmentCode.Length - 1);
sort = departmentCode;
}
else
{
sort = checkedComBoxCol.EditValue.ToString().Trim();
}
}
if (!string.IsNullOrEmpty(sort))
{
if (rad1.Checked)
{
sql += " order by " + sort + " ";
}
else
{
sql += " order by " + sort + " desc ";
}
}
try
{
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count == 0)
{
this.gridControl1.DataSource = null;
if (GlobalParameters.iLanugage > 10)
{
MessageBox.Show("Data is empty", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("没有数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return;
}
this.gridControl1.DataSource = dt;
list.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
CurrentStockModel m = new CurrentStockModel();
m.WareCode = ClsSystem.gnvl(dt.Rows[i]["wareCode"], "");
m.WareName = ClsSystem.gnvl(dt.Rows[i]["wareName"], "");
m.InvCode = ClsSystem.gnvl(dt.Rows[i]["invCode"], "");
m.InvName = ClsSystem.gnvl(dt.Rows[i]["invName"], "");
m.UnitName = ClsSystem.gnvl(dt.Rows[i]["unitName"], "");
m.InvStd = ClsSystem.gnvl(dt.Rows[i]["specification"], "");
m.ItemName = ClsSystem.gnvl(dt.Rows[i]["ItemName"], "");
m.Materials = ClsSystem.gnvl(dt.Rows[i]["Materials"], "");
m.Color = ClsSystem.gnvl(dt.Rows[i]["Color"], "");
m.Dimension = ClsSystem.gnvl(dt.Rows[i]["Dimension"], "");
m.COO = ClsSystem.gnvl(dt.Rows[i]["COO"], "");
m.cCost = Convert.ToDecimal(ClsSystem.gnvl(dt.Rows[i]["latestCost"], "0"));
m.cSalePrice = Convert.ToDecimal(ClsSystem.gnvl(dt.Rows[i]["retailPrice"], "0"));
m.Qty = Convert.ToDecimal(ClsSystem.gnvl(dt.Rows[i]["qty"], "0"));
m.r = Convert.ToDecimal(ClsSystem.gnvl(dt.Rows[i]["r"], "0"));
m.c = Convert.ToDecimal(ClsSystem.gnvl(dt.Rows[i]["c"], "0"));
m.k = Convert.ToDecimal(ClsSystem.gnvl(dt.Rows[i]["k"], "0"));
if (DBNull.Value != DbHelperSQL.GetNull(dt.Rows[i]["FileContent"]) && !string.IsNullOrEmpty(ClsSystem.gnvl(dt.Rows[i]["FileContent"], "")))
{
m.Photo = (byte[])dt.Rows[i]["FileContent"];
}
else
{
m.Photo = null;
}
list.Add(m);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Import()
{
SaveFileDialog sf = new SaveFileDialog();
sf.Filter = "Excel文件|*.xls";
if (sf.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
string fileName = sf.FileName;
CurrentStockModel[] model = list.ToArray();
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet(this.Text);
IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("序号");
if (GlobalParameters.iLanugage > 10)
{
for (int i = 0; i < gridView1.Columns.Count; i++)
{
if (gridView1.Columns[i].Visible && (gridView1.Columns[i].Caption != "Latest Cost" && gridView1.Columns[i].Caption != "Retail Price"))
{
rowHeader.CreateCell(i, CellType.STRING).SetCellValue(gridView1.Columns[i].Caption);
sheet.SetColumnWidth(i, 20 * 256);
}
}
}
else
{
for (int i = 0; i < gridView1.Columns.Count; i++)
{
if (gridView1.Columns[i].Visible && (gridView1.Columns[i].Caption != "最新成本" && gridView1.Columns[i].Caption != "零售价"))
{
rowHeader.CreateCell(i, CellType.STRING).SetCellValue(gridView1.Columns[i].Caption);
sheet.SetColumnWidth(i, 20 * 256);
}
}
}
if (gridColumn15.Visible && gridColumn14.Visible)
{
rowHeader.CreateCell(gridColumn15.AbsoluteIndex, CellType.STRING).SetCellValue(gridColumn15.Caption);
rowHeader.CreateCell(gridColumn14.AbsoluteIndex, CellType.STRING).SetCellValue(gridColumn14.Caption);
}
else
{
if (gridColumn15.Visible && gridColumn14.Visible == false)
{
rowHeader.CreateCell(gridColumn15.AbsoluteIndex, CellType.STRING).SetCellValue(gridColumn15.Caption);
}
else if (gridColumn14.Visible && gridColumn15.Visible == false)
{
rowHeader.CreateCell(gridColumn15.AbsoluteIndex, CellType.STRING).SetCellValue(gridColumn14.Caption);
}
}
try
{
ICellStyle style = book.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
string bakPath = string.Empty;
if (string.IsNullOrWhiteSpace(this.txtDown.Text))
{
string fileM = System.IO.Path.Combine(CommonHelper.currenctDir, "tmp");
if (!Directory.Exists(fileM))
{
Directory.CreateDirectory(fileM);
}
string[] str = Directory.GetFiles(fileM);
for (int i = 0; i < str.Length; i++)
{
if (str[i].Contains(".jpg"))
{
File.Delete(str[i]);
}
}
bakPath = fileM;
}
else
{
bakPath = this.txtDown.Text;
}
for (int i = 0; i < model.Length; i++)
{
CurrentStockModel em = model[i];
IRow row = sheet.CreateRow(i + 1);
int iwdith = 0;
if (null != em.Photo && !string.IsNullOrEmpty(ClsSystem.gnvl(em.Photo, "")))
{
string picPath = System.IO.Path.Combine(bakPath, em.InvCode + ".jpg");
if (!File.Exists(picPath))
{
System.IO.File.WriteAllBytes(picPath, em.Photo);
}
using (System.Drawing.Image img = System.Drawing.Image.FromFile(picPath))
{
int iheight = img.Height * 10;
row.Height = (short)iheight;
iwdith = Convert.ToInt32(img.Width);
}
}
else
{
row.Height = 30 * 10;
}
ICell cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(em.WareCode);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(em.WareName);
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(em.InvCode);
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(em.InvName);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue(em.InvStd);
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue(em.ItemName);
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(em.Materials);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(em.Color);
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(em.Dimension);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(em.COO);
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(em.UnitName);
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(em.Qty.ToString());
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(em.r.ToString());
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(em.c.ToString());
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(em.k.ToString());
cell = row.CreateCell(16);
cell.CellStyle = style;
if (em.Photo != null)
{
int pictureIdx = book.AddPicture(em.Photo, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//add a picture (0, 0, 1023, 0, 6, 6 + i, 6, i+1);
//HSSFClientAnchor anchor = new HSSFClientAnchor(1, 1, 1023, 1, 6,i+1, 6, i+2);
// HSSFClientAnchor anchor = new HSSFClientAnchor(50, 50, iwdith * 4 >= 1023 ? 1000 : iwdith * 4, 0, 13, i + 1, 13, i + 2);
HSSFClientAnchor anchor = new HSSFClientAnchor(50, 20, iwdith * 4 >= 1023 ? 1000 : iwdith * 4, 0, 16, i + 1, 16, i + 2);
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 6, i + 1, 6, i + 2);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
// pict.Resize();
}
if (gridColumn15.Visible && gridColumn14.Visible)
{
cell = row.CreateCell(17);
cell.CellStyle = style;
cell.SetCellValue(em.cCost.ToString());
cell = row.CreateCell(18);
cell.CellStyle = style;
cell.SetCellValue(em.cSalePrice.ToString());
}
else
{
if (gridColumn15.Visible && gridColumn14.Visible == false)
{
cell = row.CreateCell(17);
cell.CellStyle = style;
cell.SetCellValue(em.cCost.ToString());
}
else if (gridColumn14.Visible && gridColumn15.Visible == false)
{
cell = row.CreateCell(17);
cell.CellStyle = style;
cell.SetCellValue(em.cSalePrice.ToString());
}
}
}
using (Stream str = File.OpenWrite(fileName))
{
book.Write(str);
}
if (GlobalParameters.iLanugage > 10)
{
MessageBox.Show("Export is suceess！");
}
else
{
MessageBox.Show("导出成功！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}