API tools faq
paste
Advertisement
Guest User

LARC Upload

a guest
Mar 31st, 2019
162
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.47 KB | None | 0 0
raw download clone embed print report
  1. /*
  2. * To change this license header, choose License Headers in Project Properties.
  3. * To change this template file, choose Tools | Templates
  4. * and open the template in the editor.
  5. */
  6. package javaapplication4;
  7.  
  8. import java.io.BufferedInputStream;
  9. import java.io.FileInputStream;
  10. import java.io.FileNotFoundException;
  11. import java.sql.Connection;
  12. import java.sql.DriverManager;
  13. import java.sql.SQLException;
  14. import java.sql.Statement;
  15. import org.apache.poi.ss.usermodel.Cell;
  16. import org.apache.poi.ss.usermodel.Row;
  17. import org.apache.poi.ss.usermodel.Sheet;
  18. import org.apache.poi.ss.usermodel.Workbook;
  19. import org.apache.poi.ss.usermodel.WorkbookFactory;
  20.  
  21. /**
  22. *
  23. * @author nikko
  24. */
  25. public class PhaseUPload {
  26.  
  27. /**
  28. * @param args the command line arguments
  29. */
  30. public static void main(String[] args) throws FileNotFoundException {
  31. // TODO code application logic here
  32. Connection con = null;
  33. int counter = 0;
  34. try {
  35. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  36. String connectionUrl = "jdbc:sqlserver://116.93.120.174;" + "databaseName=WaterBilling_LARC;user=sa;password=$QLhti2018!;";
  37. con = DriverManager.getConnection(connectionUrl);
  38. System.out.println("SUCCESS");
  39. } catch (SQLException e) {
  40. System.out.println("SQL Exception: " + e.toString());
  41. } catch (ClassNotFoundException cE) {
  42. System.out.println("Class Not Found Exception: " + cE.toString());
  43. }
  44. // BufferedInputStream bis = new BufferedInputStream(new FileInputStream("C:\\Users\\nikko\\Desktop\\projecttemplate.xlsx"));
  45.  
  46. BufferedInputStream bis = new BufferedInputStream(new FileInputStream("C:\\Users\\chris\\Desktop\\ReadingProoflist.xlsx"));
  47.  
  48. try {
  49. Workbook wb;
  50. wb = WorkbookFactory.create(bis);
  51. Sheet sheet = wb.getSheetAt(0);
  52. String a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, custno, pres = "";
  53.  
  54. int rowStart = sheet.getFirstRowNum();
  55. int rowEnd = sheet.getLastRowNum();
  56.  
  57. for (int count = rowStart ; count <= rowEnd; count++) {
  58. Row row = sheet.getRow(count);
  59.  
  60. Cell cell = row.getCell(0);
  61. if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  62.  
  63. custno = cell.getStringCellValue();
  64. } else {
  65.  
  66. custno = String.valueOf(cell.getNumericCellValue());
  67. }
  68. cell = row.getCell(4);
  69.  
  70. if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  71.  
  72. pres = cell.getStringCellValue();
  73. } else {
  74.  
  75. pres = String.valueOf((int) cell.getNumericCellValue());
  76. }
  77. // }
  78. // cell = row.getCell(2);
  79. //
  80. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  81. //
  82. // c = cell.getStringCellValue();
  83. // } else {
  84. //
  85. // c = String.valueOf(cell.getNumericCellValue());
  86. // }
  87. // cell = row.getCell(3);
  88. //
  89. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  90. //
  91. // d = cell.getStringCellValue();
  92. // } else {
  93. //
  94. // d = String.valueOf(cell.getNumericCellValue());
  95. // }
  96. // cell = row.getCell(4);
  97. //
  98. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  99. //
  100. // e = cell.getStringCellValue();
  101. // } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
  102. //
  103. // e = String.valueOf(cell.getNumericCellValue());
  104. // } else {
  105. // e = String.valueOf(cell.getDateCellValue());
  106. // }
  107. // cell = row.getCell(5);
  108. //
  109. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  110. //
  111. // f = cell.getStringCellValue();
  112. // } else {
  113. //
  114. // f = String.valueOf((int) cell.getNumericCellValue());
  115. // }
  116. // cell = row.getCell(6);
  117. //
  118. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  119. //
  120. // g = cell.getStringCellValue();
  121. // } else {
  122. //
  123. // g = String.valueOf((int) cell.getNumericCellValue());
  124. // }
  125. // cell = row.getCell(7);
  126. //
  127. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  128. //
  129. // h = cell.getStringCellValue();
  130. // } else {
  131. //
  132. // h = String.valueOf((int) cell.getNumericCellValue());
  133. // }
  134. // cell = row.getCell(8);
  135. //
  136. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  137. //
  138. // i = cell.getStringCellValue();
  139. // } else {
  140. //
  141. // i = String.valueOf(cell.getNumericCellValue());
  142. // }
  143. // cell = row.getCell(9);
  144. //
  145. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  146. //
  147. // j = cell.getStringCellValue();
  148. // } else {
  149. //
  150. // j = String.valueOf(cell.getNumericCellValue());
  151. // }
  152. // cell = row.getCell(10);
  153. //
  154. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  155. //
  156. // k = cell.getStringCellValue();
  157. // } else {
  158. //
  159. // k = String.valueOf(cell.getNumericCellValue());
  160. // }
  161. // cell = row.getCell(11);
  162. //
  163. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  164. //
  165. // l = cell.getStringCellValue();
  166. // } else {
  167. //
  168. // l = String.valueOf(cell.getNumericCellValue());
  169. // }
  170. // cell = row.getCell(12);
  171. //
  172. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  173. //
  174. // m = cell.getStringCellValue();
  175. // } else {
  176. //
  177. // m = String.valueOf(cell.getNumericCellValue());
  178. // }
  179. // cell = row.getCell(13);
  180. //
  181. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  182. //
  183. // n = cell.getStringCellValue();
  184. // } else {
  185. //
  186. // n = String.valueOf(cell.getNumericCellValue());
  187. // }
  188. // cell = row.getCell(14);
  189. //
  190. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  191. //
  192. // o = cell.getStringCellValue();
  193. // } else {
  194. //
  195. // o = String.valueOf(cell.getNumericCellValue());
  196. // }
  197. // cell = row.getCell(15);
  198. //
  199. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  200. //
  201. // p = cell.getStringCellValue();
  202. // } else {
  203. //
  204. // p = String.valueOf(cell.getNumericCellValue());
  205. // }
  206. // cell = row.getCell(16);
  207. //
  208. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  209. //
  210. // q = cell.getStringCellValue();
  211. // } else {
  212. //
  213. // q = String.valueOf(cell.getNumericCellValue());
  214. // }
  215. // cell = row.getCell(18);
  216. //
  217. // if (cell.getCellTypssse() == Cell.CELL_TYPE_STRING) {
  218. //
  219. // r = cell.getStringCellValue();
  220. // } else {
  221. //
  222. // r = String.valueOf(cell.getNumericCellValue());
  223. // }
  224. // cell = row.getCell(19);
  225. //
  226. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  227. //
  228. // s = cell.getStringCellValue();
  229. // } else {
  230. //
  231. // s = String.valueOf(cell.getNumericCellValue());
  232. // }
  233. // cell = row.getCell(20);
  234. //
  235. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  236. //
  237. // t = cell.getStringCellValue();
  238. // } else {
  239. //
  240. // t = String.valueOf(cell.getNumericCellValue());
  241. // }
  242. // cell = row.getCell(21);
  243. //
  244. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  245. //
  246. // u = cell.getStringCellValue();
  247. // } else {
  248. //
  249. // u = String.valueOf(cell.getNumericCellValue());
  250. // }
  251. // cell = row.getCell(22);
  252. //
  253. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  254. //
  255. // v = cell.getStringCellValue();
  256. // } else {
  257. //
  258. // v = String.valueOf(cell.getNumericCellValue());
  259. // }
  260. // cell = row.getCell(23);
  261. //
  262. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  263. //
  264. // w = cell.getStringCellValue();
  265. // } else {
  266. //
  267. // w = String.valueOf(cell.getNumericCellValue());
  268. // }
  269. // cell = row.getCell(24);
  270. //
  271. // if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  272. //
  273. // x = cell.getStringCellValue();
  274. // } else {
  275. //
  276. // x = String.valueOf(cell.getNumericCellValue());
  277. // } cell = row.getCell(6);
  278.  
  279. try {
  280.  
  281. // String custappquery = "insert into CustomerApplicationModels values(NEWID(),'','','','','','','','','')";
  282. //
  283. // String qry = "insert into CustomerModels values (NEWID()\n"
  284. // + ",(select ApplicationID from CustomerApplicationModels where AccountName ='BUENDICHO, FELICIDAD'),\n"
  285. // + "'6B71FE87-2E03-43C5-A9AA-8898583C8126',\n"
  286. // + "'32ECAD03-D42D-4F42-B38B-5B699BA76BC1'\n"
  287. // + ",'252-12-512'\n"
  288. // + ",'0',\n"
  289. // + "'2008-01-17 00:00:00.000',\n"
  290. // + "'1',\n"
  291. // + "'0',\n"
  292. // + "'2008-01-17 00:00:00.000',\n"
  293. // + "'',\n"
  294. // + "'123',\n"
  295. // + "(select MeterBrandID from MeterBrandModels where MeterBrand='EXCEL'),\n"
  296. // + "'C3E4EE0F-2F72-4C9B-ADF2-B1A79D725783',\n"
  297. // + "'3'\n"
  298. // + ",'')";
  299. Statement statement = con.createStatement();
  300. // String query = "insert into CustomerDiscount_Subsidy values(newID(),(select CustomerID from CustomerModels where CustomerNo ='" + a + "'),'10')";
  301. // System.out.println(query);
  302. // String query=" insert into ProjectModels values(NEWID(),'"+a+"','"+b+"','"+c+"','"+d+"','"+e+"','"+f+"','"+g+"','1')";
  303. //
  304. //
  305. // System.out.println(query);
  306. // statement.executeUpdate(query);
  307. // String query="INSERT INTO [dbo].[TitleModel]\n" +
  308. //" ([TitleID]\n" +
  309. //" ,[TCT_OCT_Number]\n" +
  310. //" ,[Address]\n" +
  311. //" ,[TitleStatus]\n" +
  312. //" ,[TitleLocation]\n" +
  313. //" ,[RDLocation]\n" +
  314. //" ,[LotArea]\n" +
  315. //" ,[Block]\n" +
  316. //" ,[Lot]\n" +
  317. //" ,[PhaseID]\n" +
  318. //" ,[ProjectID]\n" +
  319. //" ,[PSDNumber]\n" +
  320. //" ,[EntityID]\n" +
  321. //" ,[RegisteredTo] \n" +
  322. //" ,[LandTDNumber]\n" +
  323. //" ,[LandUseClassification] \n" +
  324. //" ,[BldgTdNumber]\n" +
  325. //" ,[BldgTdOwner] \n" +
  326. //" ,[LaunchStatus]\n" +
  327. //" ,[RedemptionPayable]\n" +
  328. //" ,[RedemptionPaid]\n" +
  329. //" ,[RedemptionValue])\n" +
  330. //" VALUES\n" +
  331. //" (NEWID()\n" +
  332. //" ,'"+a+"'\n" +
  333. //" ,'"+b+"'\n" +
  334. //" ,'Active'\n" +
  335. //" ,'"+c+"'\n" +
  336. //" ,'"+e+"'\n" +
  337. //" ,'"+f+"'\n" +
  338. //" ,'"+g+"'\n" +
  339. //" ,'"+h+"'\n" +
  340. //" ,'8490F2CA-602E-4313-A554-19ED3FFB4565'\n" +
  341. //" ,'5C264230-9797-4C5B-954C-94BA1A765932'\n" +
  342. //" ,'"+i+"'\n" +
  343. //" ,'BAE4F522-493C-4F60-A8B8-46DA44310328'\n" +
  344. //" ,'"+l+"'\n" +
  345. //" ,'"+j+"'\n" +
  346. //" ,''\n" +
  347. //" ,'"+k+"'\n" +
  348. //" ,''\n" +
  349. //" ,'LAUNCHED'\n" +
  350. //" ,''\n" +
  351. //" ,'"+m+"'\n" +
  352. //" ,'"+d+"'\n" +
  353. //")";
  354.  
  355. //
  356. // String query = "insert into ProductModels (\n"
  357. // + "[ProductID]\n"
  358. // + " ,[YearModel]\n"
  359. // + " ,[HouseModel]\n"
  360. // + " ,[HouseFinish]\n"
  361. // + " ,[ConstructionModel]\n"
  362. // + " ,[FloorArea]\n"
  363. // + " ,[CostPerSquareMeter]\n"
  364. // + " ,[ConstructionCost]\n"
  365. // + " ,[ContractAmount]\n"
  366. // + " ,[TotalOSM]\n"
  367. // + " ,[Tjoist]\n"
  368. // + " ,[Roof]\n"
  369. // + " ,[Paint]\n"
  370. // + " ,[Others]\n"
  371. // + " ,[PricePerSquareMeter]\n"
  372. // + " ,[HousePrice]\n"
  373. // + " ,[ReservationFee]\n"
  374. // + " ,[HdmfAppraisal]\n"
  375. // +" ,[type]\n"
  376. // +
  377. // " ,[PRICE]\n" +
  378. // " ,[AppraisedValue]\n" +
  379. // " ,EffectivityDate"
  380. // + ") values (NEWID(),'" + a + "','" + b + "','" + c + "','" + d + "','" + (int)Double.parseDouble(e) + "','" + f + "',"
  381. // + "'" + g + "','" + h + "','" + i + "','" + j + "','" + k + "',"
  382. // + "'" + l + "','" + m + "','" + n + "','" + o + "','" + p + "','" + q + "','NONE',0,0,'08/07/2018')";
  383. String query="SET IDENTITY_INSERT BillingTable OFF " +
  384. "INSERT INTO BillingTable " +
  385. "([CustomerNo],[ReadingDate],[PresentReading],[DateAdded],[DueDate],[synced]) " +
  386. "VALUES" +
  387. "('"+custno+"'" +
  388. ",GETDATE()," +
  389. "'" + pres + "'," +
  390. "GETDATE()," +
  391. "'01/01/1990','1')";
  392. System.out.println(query);
  393. statement.executeUpdate(query);
  394. } catch (Exception x) {
  395. System.out.println(x.toString());
  396. }
  397. }
  398. System.out.println(rowEnd);
  399.  
  400. } catch (Exception e) {
  401. System.out.println("error%");
  402.  
  403.  
  404.  
  405.  
  406.  
  407.  
  408.  
  409.  
  410.  
  411.  
  412. System.out.println(e.toString());
  413. }
  414. }
  415. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!