Exemplo n.º 1
0
        ///2转DB
        static void Change2DB(SQLiteDBHelper sqliteDBHelper, string ExcelPath)
        {
            IWorkbook  workbook   = null;
            FileStream fileStream = new FileStream(ExcelPath, FileMode.Open, FileAccess.Read);
            string     tableName  = Path.GetFileNameWithoutExtension(ExcelPath);

            if (ExcelPath.IndexOf(".xlsx") > 0)          // 2007版本
            {
                workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
            }
            else if (ExcelPath.IndexOf(".xls") > 0)      // 2003版本
            {
                workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
            }
            else
            {
                return;
            }
            ISheet sheet = workbook.GetSheetAt(0);

            if (sheet != null)
            {
                //创建标投
                IRow rowItem1 = sheet.GetRow(2);
                IRow rowItem2 = sheet.GetRow(3);

                List <string> ColumnsName = new List <string>();
                List <string> ColumnsType = new List <string>();


                List <string> CSType    = new List <string>();
                int           cellCount = rowItem1.LastCellNum;
                for (int k = 0; k < cellCount; k++)
                {
                    ICell cell = rowItem1.GetCell(k);
                    ICell cel2 = rowItem2.GetCell(k);
                    if (cell != null)
                    {
                        string cellValue = cell.StringCellValue;
                        ColumnsName.Add(cel2.StringCellValue);
                        ColumnsType.Add(CS2DbType(cell.StringCellValue));
                        CSType.Add(GetCSharpType(cell.StringCellValue));
                    }
                }
                sqliteDBHelper.CreateTable(tableName, ColumnsName.ToArray(), ColumnsType.ToArray());

                using (DbTransaction transaction = sqliteDBHelper.connection.BeginTransaction())
                {
                    using (DbCommand cmd = sqliteDBHelper.connection.CreateCommand())
                    {
                        int startRow = 4;
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; i++)
                        {
                            IRow          rowItem    = sheet.GetRow(i);
                            List <string> ContentStr = new List <string>();
                            bool          caInsert   = true;
                            for (int k = 0; k < rowItem.LastCellNum; k++)
                            {
                                ICell cellItem = rowItem.GetCell(k);
                                if (cellItem != null && !string.IsNullOrEmpty(cellItem.ToString()))
                                {
                                    ContentStr.Add(cellItem.ToString());
                                }
                                else
                                {
                                    caInsert = false;
                                    break;
                                }
                            }
                            if (caInsert)
                            {
                                //解决SQL注入漏洞  cmd是SqlCommand对象
                                cmd.CommandText = sqliteDBHelper.GetInsertValueStr(tableName, ColumnsName.ToArray());// @"select count(*) from UserInfo where UserName=@UserName and UserPwd=@UserPwd";

                                for (int k = 0; k < ColumnsName.Count; k++)
                                {
                                    DbParameter para = cmd.CreateParameter();
                                    para.ParameterName = "@" + ColumnsName[k] + ""; //SQL参数化
                                    para.Value         = ContentStr[k];             //SQL参数化
                                    cmd.Parameters.Add(para);
                                }
                                cmd.ExecuteScalar();
                            }
                        }
                    }
                    transaction.Commit();
                }
                CreatCs(Path.GetFileNameWithoutExtension(ExcelPath), Path.Combine(CsPath, "CS_" + Path.GetFileNameWithoutExtension(ExcelPath) + ".cs"), ColumnsName.ToArray(), CSType);
            }
        }