API tools faq
paste
Guest User

Untitled

a guest
Dec 5th, 2017
112
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.40 KB | None | 0 0
raw download clone embed print report
  1. Validations like:
  2. 1. The cell data should be only numeric
  3. 2. The cell data should be in a specific format xxx-xxx
  4. 3. The cell data should be a date in yyyy-mm-dd format
  5.  
  6. > package src;
  7. >
  8. > import java.io.FileInputStream; import java.io.IOException; import
  9. > java.sql.Connection; import java.sql.DriverManager; import
  10. > java.sql.PreparedStatement; import
  11. > org.apache.poi.poifs.filesystem.POIFSFileSystem; //import
  12. > org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.*;
  13. >
  14. > import org.apache.poi.xssf.usermodel.XSSFWorkbook; import
  15. > org.apache.poi.xssf.usermodel.XSSFSheet;
  16. >
  17. > public class DataKauai {
  18. >
  19. > public static void main(String[] args) throws Exception {
  20. >
  21. > try {
  22. >
  23. > Class forName = Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
  24. > Connection con = null;
  25. > con = DriverManager.getConnection("jdbc:sybase:Tds:tkbgssvt1:4105",
  26. > "kauai_rwu","rwu_kauai");
  27. > System.out.println("Database connected to Sybase..");
  28. > con.setAutoCommit(false);
  29. > PreparedStatement pstm = null;
  30. > FileInputStream input = new FileInputStream("C:\Users\suresnar\Desktop\Mizu_FGloss\Kauai_IN_Table_test.xlsx");
  31. > // POIFSFileSystem fs = new POIFSFileSystem(input);
  32. >
  33. > XSSFWorkbook workbook = new XSSFWorkbook(input);
  34. > XSSFSheet sheet = workbook.getSheetAt(0);
  35. > Row row;
  36. > for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  37. > row = (Row) sheet.getRow(i);
  38. > DataFormatter formatter = new DataFormatter();
  39. > String TradeAction = formatter.formatCellValue(row.getCell(0));
  40. > String TradeID = formatter.formatCellValue(row.getCell(1));
  41. > String Version =formatter.formatCellValue(row.getCell(2));
  42. > String TradeType = formatter.formatCellValue(row.getCell(3));
  43. > String Book = formatter.formatCellValue(row.getCell(4));
  44. > String Direction = row.getCell(5).getStringCellValue();
  45. > String SafekeepFlag = row.getCell(6).getStringCellValue();
  46. > String OurSettlePlace = formatter.formatCellValue(row.getCell(7));
  47. > String OurSettleDepot = row.getCell(8).getStringCellValue();
  48. > String TheirSettlePlace = row.getCell(9).getStringCellValue();
  49. > String TheirSettleDepot = row.getCell(10).getStringCellValue();
  50. > String BrokerCode = row.getCell(11).getStringCellValue();
  51. > String CustomerCode =formatter.formatCellValue(row.getCell(12));
  52. > String ProductCode = formatter.formatCellValue(row.getCell(13));
  53. > String TradeDate =formatter.formatCellValue(row.getCell(14));
  54. > String TradeTime = formatter.formatCellValue(row.getCell(15));
  55. > String CreditPerson = row.getCell(16).getStringCellValue();
  56. > String Quantity = formatter.formatCellValue(row.getCell(17));
  57. > String Factor = formatter.formatCellValue(row.getCell(18));
  58. > String ActualQuantity = formatter.formatCellValue(row.getCell(19));
  59. > String SettleDate = formatter.formatCellValue(row.getCell(20));
  60. > String Price = formatter.formatCellValue(row.getCell(21));
  61. > String TradeCcy = row.getCell(22).getStringCellValue();
  62. > String TradeValue = formatter.formatCellValue(row.getCell(23));
  63. > String AccDays = formatter.formatCellValue(row.getCell(24));
  64. > String TradeAICcy = formatter.formatCellValue(row.getCell(25));
  65. > String TradeAI = formatter.formatCellValue(row.getCell(26));
  66. > String SettleAmt = formatter.formatCellValue(row.getCell(27));
  67. > String RateCalc = formatter.formatCellValue(row.getCell(28));
  68. > String FxRate = formatter.formatCellValue(row.getCell(29));
  69. > String SettleCcy = formatter.formatCellValue(row.getCell(30));
  70. > String SettleAmtInFX = formatter.formatCellValue(row.getCell(31));
  71. > String Memo1 = row.getCell(32).getStringCellValue();
  72. > String Memo2 = row.getCell(33).getStringCellValue();
  73. > String Memo3 = row.getCell(34).getStringCellValue();
  74. > String Memo4 = row.getCell(35).getStringCellValue();
  75. > String Memo5 = row.getCell(36).getStringCellValue();
  76. > String Remark1 = row.getCell(37).getStringCellValue();
  77. > String Remark2 = row.getCell(38).getStringCellValue();
  78. > String SelfStBrCode = formatter.formatCellValue(row.getCell(39));
  79. > String SelfStBrSubCode = formatter.formatCellValue(row.getCell(40));
  80. > String CustStBrCode = formatter.formatCellValue(row.getCell(41));
  81. > String CustStClCode = formatter.formatCellValue(row.getCell(42));
  82. > String CustStSubCode = formatter.formatCellValue(row.getCell(43));
  83. > String DiscretionFlg = formatter.formatCellValue(row.getCell(44));
  84. > String InputTime = formatter.formatCellValue(row.getCell(45));
  85. > String UpdateTime = formatter.formatCellValue(row.getCell(46));
  86. > String IFStatus = formatter.formatCellValue(row.getCell(47));
  87. > String IFVersion = formatter.formatCellValue(row.getCell(48));
  88. > String IFDate = formatter.formatCellValue(row.getCell(49));
  89. > String IFTime = formatter.formatCellValue(row.getCell(50));
  90. > String AzIFStatus = formatter.formatCellValue(row.getCell(51));
  91. > String AzIFVersion =formatter.formatCellValue(row.getCell(52));
  92. > String AzIFTime =formatter.formatCellValue(row.getCell(53));
  93. > String AzCKTime =formatter.formatCellValue(row.getCell(54));
  94. >
  95. >
  96. >
  97. > // String email = row.getCell(3).getStringCellValue();
  98. >
  99. > if(IFTime==" ")
  100. > {
  101. > if(IFVersion==" ")
  102. > {
  103. > if(IFDate==" ")
  104. > {
  105. > if(IFStatus==" ")
  106. > {
  107. > if(TradeID.length()==14)
  108. > {
  109. >
  110. > if(TradeDate>=SettleDate)
  111. > {
  112. >
  113. >
  114. > String sql = "INSERT INTO FB_KAUAI_IN (TradeAction,TradeID,Version,TradeType,Book,Direction,SafekeepFlag,OurSettlePlace,"
  115. > + "OurSettleDepot,TheirSettlePlace,TheirSettleDepot,BrokerCode,"
  116. > + "CustomerCode,ProductCode,TradeDate,TradeTime,CreditPerson,Quantity,Factor,"
  117. > + "ActualQuantity,SettleDate,Price,TradeCcy,TradeValue,AccDays,TradeAICcy,"
  118. > + "TradeAI,SettleAmt,RateCalc,FxRate,SettleCcy,SettleAmtInFX,Memo1, Memo2, "
  119. > + "Memo3, Memo4, Memo5, Remark1, Remark2 ,SelfStBrCode, SelfStBrSubCode, "
  120. > + "CustStBrCode, CustStClCode, CustStSubCode, DiscretionFlg, InputTime, "
  121. > + "UpdateTime, IFStatus, IFVersion, IFDate, IFTime, AzIFStatus, "
  122. > + "AzIFVersion,AzIFTime,AzCKTime) VALUES('"+TradeAction+"','"+TradeID+"','"+Version+"', "
  123. > + "'"+TradeType+"','"+Book+"','"+Direction+"','"+SafekeepFlag+"',"
  124. > + "'"+OurSettlePlace+"','"+OurSettleDepot+"','"+TheirSettlePlace+"',"
  125. > + "'"+TheirSettleDepot+"','"+BrokerCode+"','"+CustomerCode+"','"+ProductCode+"',"
  126. > + "'"+TradeDate+"','"+TradeTime+"','"+CreditPerson+"','"+Quantity+"','"+Factor+"',"
  127. > + "'"+ActualQuantity+"','"+SettleDate+"','"+Price+"','"+TradeCcy+"',"
  128. > + "'"+TradeValue+"','"+AccDays+"','"+TradeAICcy+"','"+TradeAI+"','"+SettleAmt+"',"
  129. > + "'"+ RateCalc+"','"+FxRate+"','"+SettleCcy+"','"+SettleAmtInFX+"','"+Memo1+"',"
  130. > + "'"+Memo2+"','"+Memo3+"','"+Memo4+"','"+Memo5+"','"+Remark1+"','"+Remark2+"',"
  131. > + "'"+SelfStBrCode+"','"+SelfStBrSubCode+"','"+CustStBrCode+"','"+CustStClCode+"',"
  132. > + "'"+CustStSubCode+"','"+DiscretionFlg+"','"+InputTime+"','"+UpdateTime+"',"
  133. > + "'"+IFStatus+"','"+IFVersion+"','"+IFDate+"','"+IFTime+"',"
  134. > + "'"+AzIFStatus+"','"+AzIFVersion+"','"+AzIFTime+"',"
  135. > + "'"+AzCKTime+"')";
  136. >
  137. >
  138. >
  139. > //System.out.println("results " +sql);
  140. > pstm = (PreparedStatement) con.prepareStatement(sql);
  141. > pstm.execute();
  142. > System.out.println("Imported rows " + i);
  143. >
  144. > }
  145. > else System.out.println("IFStatus not null");
  146. > }
  147. > else
  148. > System.out.println("IFDate is not null");
  149. > }
  150. > else
  151. > System.out.println("IFVersion is not null");
  152. > }
  153. > else
  154. > System.out.println("IFTime is null");
  155. > }
  156. > else
  157. > System.out.println("TradeID is not 14 letters");
  158. > }
  159. > else
  160. > System.out.println("Settele date is more than Trade ID");
  161. >
  162. > con.commit();
  163. > pstm.close();
  164. > con.close();
  165. > input.close();
  166. > System.out.println("Success import excel to mysql table");
  167. >
  168. >
  169. > }
  170. >
  171. > }
  172. > catch (IOException e) {
  173. > e.printStackTrace();
  174. > }
  175. > catch(Exception e){
  176. > //Handle errors for Class.forName
  177. > e.printStackTrace();
  178. > }
  179. > } }
Add Comment
Please, Sign In to add comment
Public Pastes
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!