using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.Data.Sql; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; namespace BaseSet { public partial class Gy_DataIn_MatePriceCus_K3 : Form { DAL.ClsK3_Customer_View oCus = new DAL.ClsK3_Customer_View();//K3¿Í»§ DAL.ClsK3_Material_View oMater = new DAL.ClsK3_Material_View(); //K3ÎïÁÏ DAL.ClsK3_Unit_View oUnit = new DAL.ClsK3_Unit_View();//K3¼ÆÁ¿µ¥Î» DAL.ClsK3_Currency_View oCur = new DAL.ClsK3_Currency_View();//K3±Ò±ð // private string fileName = null; //ÎļþÃû private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public Gy_DataIn_MatePriceCus_K3() { InitializeComponent(); } private void initHeadCaption() { } private void yl_Click(object sender, EventArgs e) { WriteClass(); } //Êý¾Ýµ¼Èë public void WriteClass() { DAL.ClsK3_MatePriceCus_Ctl BillNew = new DAL.ClsK3_MatePriceCus_Ctl(); int sHCusNumberCol = DBUtility.Gy_BaseFun.Fun_GetCol("¿Í»§´úÂë", grdmain); int sHMaterNumberCol = DBUtility.Gy_BaseFun.Fun_GetCol("ÎïÁÏ´úÂë", grdmain); int sHUnitNumberCol = DBUtility.Gy_BaseFun.Fun_GetCol("¼ÆÁ¿µ¥Î»´úÂë", grdmain); int sHBegQtyCol = DBUtility.Gy_BaseFun.Fun_GetCol("¿ªÊ¼ÊýÁ¿", grdmain); int sHEndQtyCol = DBUtility.Gy_BaseFun.Fun_GetCol("½áÊøÊýÁ¿", grdmain); int sHPriceCol = DBUtility.Gy_BaseFun.Fun_GetCol("µ¥¼Û", grdmain); int sHCurNumberCol = DBUtility.Gy_BaseFun.Fun_GetCol("±Ò±ð´úÂë", grdmain); int sHBegDateCol = DBUtility.Gy_BaseFun.Fun_GetCol("¿ªÊ¼ÈÕÆÚ", grdmain); int sHEndDateCol = DBUtility.Gy_BaseFun.Fun_GetCol("ʧЧÈÕÆÚ", grdmain); int sHCheckedCol = DBUtility.Gy_BaseFun.Fun_GetCol("ÉóºË±ê¼Ç", grdmain); int sHRemarkCol = DBUtility.Gy_BaseFun.Fun_GetCol("±¸×¢", grdmain); for (int i = 0; i <= grdmain.Rows.Count - 1; i++) { if (oCus.GetInfoByNumber(DBUtility.ClsPub.isStrNull(grdmain.Rows[i].Cells[sHCusNumberCol].Value))) { BillNew.oModel.HCusID = oCus.omodel.HItemID; } else { BillNew.oModel.HCusID = 0; } // if (oMater.GetInfoByNumber(DBUtility.ClsPub.isStrNull(grdmain.Rows[i].Cells[sHMaterNumberCol].Value))) { BillNew.oModel.HMaterID = oMater.omodel.HItemID; } else { BillNew.oModel.HMaterID = 0; } // if (oUnit.GetInfoByNumber(DBUtility.ClsPub.isStrNull(grdmain.Rows[i].Cells[sHUnitNumberCol].Value))) { BillNew.oModel.HUnitID = oUnit.omodel.HItemID; } else { BillNew.oModel.HUnitID = 0; } // if (oCur.GetInfoByNumber(DBUtility.ClsPub.isStrNull(grdmain.Rows[i].Cells[sHCurNumberCol].Value))) { BillNew.oModel.HCurID = oCur.omodel.HItemID; } else { BillNew.oModel.HCurID = 0; } // BillNew.oModel.HBegQty = DBUtility.ClsPub.isDoule(grdmain.Rows[i].Cells[sHBegQtyCol].Value); BillNew.oModel.HEndQty = DBUtility.ClsPub.isDoule(grdmain.Rows[i].Cells[sHEndQtyCol].Value); BillNew.oModel.HPrice = DBUtility.ClsPub.isDoule(grdmain.Rows[i].Cells[sHPriceCol].Value); BillNew.oModel.HBegDate = DBUtility.ClsPub.isDate(grdmain.Rows[i].Cells[sHBegDateCol].Value); BillNew.oModel.HEndDate = DBUtility.ClsPub.isDate(grdmain.Rows[i].Cells[sHEndDateCol].Value); BillNew.oModel.HChecked = DBUtility.ClsPub.isBool(grdmain.Rows[i].Cells[sHCheckedCol].Value); BillNew.oModel.HRemark = "EXCELµ¼Èë"; BillNew.AddNew_K3(); } MessageBox.Show("±£´æÍê±Ï£¡"); } //дÈëÀà ´Ó¿Ø¼þ private bool Mater_WriteClass(int row) { // return true; } private void tc_Click(object sender, EventArgs e) { this.Close(); } private void K3_OutMachFeeDataIn_Load(object sender, EventArgs e) { initHeadCaption(); } public string ExcelPath; /// /// ½«excelÖеÄÊý¾Ýµ¼Èëµ½DataTableÖÐ /// /// excel·¾¶ /// excel¹¤×÷±¡sheetµÄÃû³Æ /// µÚÒ»ÐÐÊÇ·ñÊÇDataTableµÄÁÐÃû /// ·µ»ØµÄDataTable public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007°æ±¾ workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003°æ±¾ workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //Èç¹ûûÓÐÕÒµ½Ö¸¶¨µÄsheetName¶ÔÓ¦µÄsheet£¬Ôò³¢ÊÔ»ñÈ¡µÚÒ»¸ösheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //Ò»ÐÐ×îºóÒ»¸öcellµÄ±àºÅ ¼´×ܵÄÁÐÊý if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //×îºóÒ»ÁеıêºÅ int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //ûÓÐÊý¾ÝµÄÐÐĬÈÏÊÇnull¡¡¡¡¡¡¡¡¡¡¡¡¡¡ DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //ͬÀí£¬Ã»ÓÐÊý¾ÝµÄµ¥Ôª¸ñ¶¼Ä¬ÈÏÊÇnull dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } DataTable d; private void button1_Click_1(object sender, EventArgs e) { if (txtHRemark.Text.Trim() == "") { txtHRemark.Text = "1"; //MessageBox.Show("ÇëÊäÈëÐèÒªµ¼ÈëµÄ¹¤×÷±¡Ãû³Æ£¡"); //return; } OpenExcelFile.Title = "Open Excel File "; OpenExcelFile.FileName = ""; OpenExcelFile.Filter = "Microsoft Office Excel Files(*.xls)|*.xls"; OpenExcelFile.ShowDialog(); if (OpenExcelFile.FileName != "") { ExcelPath = OpenExcelFile.FileName; d = ExcelToDataTable(ExcelPath, txtHRemark.Text.Trim(), true); grdmain.DataSource = d.DefaultView; } else { ExcelPath = ""; } } private Int32 Fun_GetCol(string sCol) { return DBUtility.Xt_BaseBillFun.Fun_GetCol(sCol, grdmain); } } }