API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 13th, 2016
41
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 55.19 KB | None | 0 0
raw download clone embed print report
  1. package mhjexcel;
  2.  
  3. import java.awt.Desktop;
  4. import java.awt.HeadlessException;
  5. import java.io.File;
  6. import java.io.FileInputStream;
  7. import java.io.FileNotFoundException;
  8. import java.io.FileOutputStream;
  9. import java.io.IOException;
  10. import java.sql.Connection;
  11. import java.sql.ResultSet;
  12. import java.sql.ResultSetMetaData;
  13. import java.sql.SQLException;
  14. import java.sql.Statement;
  15. import java.util.Map;
  16. import java.util.logging.Level;
  17. import java.util.logging.Logger;
  18. import javax.swing.JFileChooser;
  19. import javax.swing.JOptionPane;
  20. import org.apache.poi.ss.util.*;
  21. import org.apache.poi.hssf.util.*;
  22. import org.apache.poi.ss.SpreadsheetVersion;
  23. import org.apache.poi.ss.usermodel.Cell;
  24. import org.apache.poi.ss.usermodel.CellStyle;
  25. import org.apache.poi.ss.usermodel.DataFormatter;
  26. import org.apache.poi.ss.usermodel.Row;
  27. import org.apache.poi.ss.util.CellReference;
  28. import org.apache.poi.ss.util.AreaReference;
  29. import org.apache.poi.xssf.usermodel.XSSFCell;
  30. import org.apache.poi.xssf.usermodel.XSSFDataFormat;
  31. import org.apache.poi.xssf.usermodel.XSSFPivotTable;
  32. import org.apache.poi.xssf.usermodel.XSSFRow;
  33. import org.apache.poi.xssf.usermodel.XSSFSheet;
  34. import org.apache.poi.xssf.usermodel.XSSFTable;
  35. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  36. import static org.apache.poi.xssf.usermodel.examples.CreatePivotTable.setCellData;
  37. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
  38. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
  39. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
  40. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
  41. import org.apache.poi.xssf.usermodel.TextDirection;
  42. import org.apache.poi.xssf.usermodel.*;
  43. import org.apache.poi.ss.usermodel.*;
  44. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  45. import org.apache.poi.ss.usermodel.RichTextString;
  46.  
  47. import java.util.Map;
  48. import java.util.HashMap;
  49. import java.util.Calendar;
  50. import java.io.FileOutputStream;
  51. import java.text.DecimalFormat;
  52. import java.text.SimpleDateFormat;
  53. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  54. import static org.apache.poi.ss.usermodel.Font.COLOR_RED;
  55.  
  56. public class Frame_Match extends javax.swing.JFrame {
  57.  
  58. public static File mhjfile;
  59. public static FileOutputStream mhjfileoutput;
  60. public static FileInputStream mhjfileinput;
  61.  
  62. public static XSSFWorkbook mhjworkbook;
  63. public static XSSFSheet mhjsheet;
  64. public static XSSFRow mhjrow;
  65. public static XSSFCell mhjcell;
  66.  
  67. public static String cellcontents;
  68. public static String mhjpath;
  69. public static String mhjextension;
  70. public static String mhjfilename;
  71. public static String mhjfulladdress;
  72.  
  73. String mhjsheetname;
  74. int rowindex;//int rowindex = Integer.parseInt(txtrow.getText());
  75. int colindex;//int colindex = Integer.parseInt(txtcol.getText());
  76. int firstrow;
  77. int lastrow;
  78. int firstcol;
  79. int lastcol;
  80. int sheetindex;//int sheetindex = Integer.parseInt(txtsheet.getText());
  81. DataFormatter mhjdataformater = new DataFormatter();
  82.  
  83. Connection con;
  84. Statement stmt;
  85. public static String sql;
  86. public static ResultSet rs;
  87.  
  88. int rsrow;
  89. int firsrstrow;
  90. int lastrsrow;
  91.  
  92. String ticker;
  93. // Double price;
  94. Double volume;
  95. Double price_balance;
  96. Double volume_balance;
  97. Integer kind;
  98. String comment;
  99. Integer date_year;
  100. Integer date_month;
  101. Integer date_day;
  102. Integer write_year;
  103. Integer write_month;
  104. Integer write_day;
  105. String treater2;
  106. String bill_or_link;
  107.  
  108. int numberofallowedemptycells;
  109.  
  110. public int outcounter;
  111. private String tablename;
  112.  
  113. int colcounter = 0;
  114. int lastcolcounter = 6;
  115. int emptyrowcounter = 0;
  116. final int allowedemptyrows = 3;
  117. int emptycellcounter = 0;
  118. final int allowedemptycols = 13;
  119. boolean flagemtycols = false;
  120. int numberoftickers = 6;
  121. final int firstticker = 1;
  122. final double unknownvalue = 0.123456789;
  123. final int number_of_manual_prices = 100;
  124.  
  125. String manual_tickers_names[] = new String[number_of_manual_prices];
  126. double manual_tickers_buys[] = new double[number_of_manual_prices];
  127. double manual_tickers_sells[] = new double[number_of_manual_prices];
  128.  
  129. public Frame_Match() {
  130. initComponents();
  131. }
  132.  
  133. public void err(Exception e) {
  134.  
  135. String sometext = String.valueOf(e);
  136. outcounter++;
  137. System.out.println("************Exception**********" + outcounter);
  138. System.out.println("(line: "
  139. + Thread.currentThread().getStackTrace()[0].getLineNumber()
  140. + "+" + Thread.currentThread().getStackTrace()[1].getLineNumber()
  141. + "+" + Thread.currentThread().getStackTrace()[2].getLineNumber()
  142. + "+" + Thread.currentThread().getStackTrace()[3].getLineNumber()
  143. + "+" + Thread.currentThread().getStackTrace()[4].getLineNumber()
  144. + ")");
  145. System.out.println("\n" + sometext);
  146. System.out.println("\n" + String.valueOf(e.getMessage()));
  147. System.out.println("\n" + String.valueOf(e.getCause()));
  148.  
  149. System.out.println("************End of Exception reporting*********");
  150.  
  151. Logger.getLogger(Frame_Ticker.class.getName()).log(Level.SEVERE, null, e);
  152. }
  153.  
  154. public void out(String sometext) {
  155. outcounter++;
  156. sometext = "\n" + outcounter + "-(line" + Thread.currentThread().getStackTrace()[2].getLineNumber() + ")==> " + sometext;
  157. System.out.println(sometext);
  158. }
  159.  
  160. public void out(Object obj) {
  161. try {
  162. String text = String.valueOf(obj);
  163. out(text);
  164. } catch (Exception e) {
  165. System.out.println("مشکل در out");
  166. }
  167. }
  168.  
  169. public static void msg(String text) {
  170. JOptionPane.showMessageDialog(null, text);
  171. }
  172.  
  173. @SuppressWarnings("unchecked")
  174. // <editor-fold defaultstate="collapsed" desc="Generated Code">
  175. private void initComponents() {
  176.  
  177. buttonGroup1 = new javax.swing.ButtonGroup();
  178. btn_get_portfolios = new javax.swing.JButton();
  179. btn_get_buy_prices = new javax.swing.JButton();
  180. btn_get_sell_prices = new javax.swing.JButton();
  181. btnoutput = new javax.swing.JButton();
  182. btncomputation = new javax.swing.JButton();
  183. jLabel1 = new javax.swing.JLabel();
  184. txt_number_of_tickers = new javax.swing.JTextField();
  185. chk_dont_merge = new javax.swing.JCheckBox();
  186.  
  187. setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
  188.  
  189. btn_get_portfolios.setText("دریافت سبد های پیشنهادی");
  190. btn_get_portfolios.addActionListener(new java.awt.event.ActionListener() {
  191. public void actionPerformed(java.awt.event.ActionEvent evt) {
  192. btn_get_portfoliosActionPerformed(evt);
  193. }
  194. });
  195.  
  196. btn_get_buy_prices.setText("دریافت قیمت های اولیه");
  197. btn_get_buy_prices.addActionListener(new java.awt.event.ActionListener() {
  198. public void actionPerformed(java.awt.event.ActionEvent evt) {
  199. btn_get_buy_pricesActionPerformed(evt);
  200. }
  201. });
  202.  
  203. btn_get_sell_prices.setText("دریافت قیمت های امروز");
  204. btn_get_sell_prices.addActionListener(new java.awt.event.ActionListener() {
  205. public void actionPerformed(java.awt.event.ActionEvent evt) {
  206. btn_get_sell_pricesActionPerformed(evt);
  207. }
  208. });
  209.  
  210. btnoutput.setText("تولید خروجی");
  211. btnoutput.addActionListener(new java.awt.event.ActionListener() {
  212. public void actionPerformed(java.awt.event.ActionEvent evt) {
  213. btnoutputActionPerformed(evt);
  214. }
  215. });
  216.  
  217. btncomputation.setText("محاسبه سبد ها");
  218. btncomputation.addActionListener(new java.awt.event.ActionListener() {
  219. public void actionPerformed(java.awt.event.ActionEvent evt) {
  220. btncomputationActionPerformed(evt);
  221. }
  222. });
  223.  
  224. jLabel1.setText("بیشینه نماد های درون سبد:");
  225.  
  226. txt_number_of_tickers.setEditable(false);
  227. txt_number_of_tickers.setText("6");
  228. txt_number_of_tickers.addKeyListener(new java.awt.event.KeyAdapter() {
  229. public void keyReleased(java.awt.event.KeyEvent evt) {
  230. txt_number_of_tickersKeyReleased(evt);
  231. }
  232. });
  233.  
  234. chk_dont_merge.setSelected(true);
  235. chk_dont_merge.setText("عدم ادغام سلول ها");
  236.  
  237. javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
  238. getContentPane().setLayout(layout);
  239. layout.setHorizontalGroup(
  240. layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  241. .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
  242. .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  243. .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  244. .addComponent(chk_dont_merge)
  245. .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
  246. .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
  247. .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
  248. .addComponent(btn_get_sell_prices, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  249. .addComponent(btn_get_buy_prices, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  250. .addComponent(btn_get_portfolios, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  251. .addComponent(btncomputation, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  252. .addComponent(btnoutput, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
  253. .addGap(10, 10, 10))
  254. .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
  255. .addComponent(txt_number_of_tickers, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
  256. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
  257. .addComponent(jLabel1)
  258. .addContainerGap()))))
  259. );
  260.  
  261. layout.linkSize(javax.swing.SwingConstants.HORIZONTAL, new java.awt.Component[] {btn_get_buy_prices, btn_get_portfolios, btn_get_sell_prices, btncomputation});
  262.  
  263. layout.setVerticalGroup(
  264. layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
  265. .addGroup(layout.createSequentialGroup()
  266. .addGap(26, 26, 26)
  267. .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
  268. .addComponent(jLabel1)
  269. .addComponent(txt_number_of_tickers, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
  270. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  271. .addComponent(btn_get_portfolios)
  272. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  273. .addComponent(btn_get_buy_prices)
  274. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  275. .addComponent(btn_get_sell_prices)
  276. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  277. .addComponent(btncomputation)
  278. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  279. .addComponent(btnoutput)
  280. .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
  281. .addComponent(chk_dont_merge)
  282. .addContainerGap(84, Short.MAX_VALUE))
  283. );
  284.  
  285. pack();
  286. }// </editor-fold>
  287.  
  288. private void btn_get_portfoliosActionPerformed(java.awt.event.ActionEvent evt) {
  289. try {
  290. mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\مسابقه.xlsx");
  291. jFileChooser1 = new javax.swing.JFileChooser();
  292. jFileChooser1.setCurrentDirectory(mhjfile);
  293. int result = jFileChooser1.showOpenDialog(this);
  294. if (result == JFileChooser.CANCEL_OPTION) {
  295. return;
  296. }
  297. mhjfile = jFileChooser1.getSelectedFile();
  298.  
  299. mhjfileinput = new FileInputStream(mhjfile);
  300. mhjworkbook = new XSSFWorkbook(mhjfileinput);
  301. mhjsheet = mhjworkbook.cloneSheet(0);
  302. XSSFDataFormat format = mhjworkbook.createDataFormat();
  303. con = mhjexcel.con;
  304. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  305. sql = "delete FROM mosabegheh";
  306. stmt.executeUpdate(sql);
  307. sql = "SELECT * FROM mosabegheh";
  308. rs = stmt.executeQuery(sql);
  309. firstrow = 1;
  310. // firstcol = 1;//تغییر داده نشود
  311. // lastcol = 5;
  312. // ________________________ حقله روی شرکت کنندگان و سطر ها
  313. for (rowindex = firstrow;; rowindex++) { //
  314. mhjrow = mhjsheet.getRow(rowindex);
  315. // ___________________________ بررسی انتهای سطر ها
  316. if (mhjrow == null) {
  317. System.out.println(
  318. //"پایان سطر های ورکشیت و پایان کار جمع آوری داده ها" +
  319. "\n تعداد سطر های خوانده شده:" + rowindex
  320. );
  321. break;
  322. }
  323. rs.moveToInsertRow();
  324. mhjcell = mhjrow.getCell(0);
  325. cellcontents = mhjdataformater.formatCellValue(mhjcell);
  326. // _____________________بررسی نمادهای نامعلوم و قیمت های دستی
  327. if (cellcontents.equals("نمادهای دستی") || cellcontents.equals("")) {
  328. int manual_tickers_counter = 0;
  329. for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
  330. mhjcell = mhjrow.getCell(tickernumber);
  331. cellcontents = mhjdataformater.formatCellValue(mhjcell);
  332. String string_array[] = new String[2];
  333. if (cellcontents != null) {
  334. string_array = cellcontents.split("(?<=\\D)(?=\\d)|(?<=\\d)(?=\\D)");
  335. }
  336. if (string_array.length != 2) {
  337. break;
  338. }
  339. String ticker = string_array[0];
  340. double price = Double.parseDouble(string_array[1]);
  341.  
  342. ticker = StandardizeTicker(ticker);
  343. if (ticker.contains("خرید")) {
  344. ticker = ticker.replaceAll("خرید", "");
  345. // rs.updateDouble("buyprice" + String.valueOf(tickernumber), price);
  346. manual_tickers_buys[manual_tickers_counter] = price;
  347. } else if (ticker.contains("فروش")) {
  348. ticker = ticker.replaceAll("فروش", "");
  349. // rs.updateDouble("buyprice" + String.valueOf(tickernumber), price);
  350. manual_tickers_sells[manual_tickers_counter] = price;
  351. } else {
  352. out("مشکل");
  353. }
  354. manual_tickers_names[manual_tickers_counter] = ticker;
  355. manual_tickers_counter++;
  356. // rs.updateString("portfolioname" + String.valueOf(tickernumber), ticker);
  357. // rs.insertRow();
  358. }
  359. continue;
  360. }
  361. // _______________ دریافت نام شرکت کنندگان _________________
  362. rs.updateString("person", cellcontents);
  363. // _______________ دریافت نماد های منتخب شرکت کنندگان _________________
  364. int emptycells = 0;
  365. for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
  366. // for (colindex = firstticker; colindex <= numberoftickers; colindex++) {
  367. mhjcell = mhjrow.getCell(tickernumber);
  368. cellcontents = mhjdataformater.formatCellValue(mhjcell);//cellcontents=mhjcell.getStringCellValue(); //mhjcell.setCellValue(cellcontents);
  369. cellcontents = cellcontents.replaceAll(",", "");
  370. cellcontents = cellcontents.replaceAll("\\s", "");
  371. cellcontents = cellcontents.replaceAll("-", "");
  372. cellcontents = cellcontents.replaceAll("\\.", "");
  373. cellcontents = cellcontents.replaceAll("ك", "ک");
  374. cellcontents = cellcontents.replaceAll("ي", "ی");
  375.  
  376. cellcontents = cellcontents.replaceAll("آ", "ا");
  377.  
  378. cellcontents = cellcontents.replaceAll("\\d", "");
  379.  
  380. cellcontents = cellcontents.replaceAll("\\(", "");
  381. cellcontents = cellcontents.replaceAll("\\)", "");
  382.  
  383. cellcontents = cellcontents.replaceAll("=", "");
  384. cellcontents = cellcontents.replaceAll("\\*", "");
  385.  
  386. if (!cellcontents.equals("")) {
  387. rs.updateString("portfolioname" + String.valueOf(tickernumber - emptycells), cellcontents);
  388. } else {
  389. emptycells++;
  390. }
  391. }
  392. rs.insertRow();
  393. }
  394. rs.close();
  395. stmt.close();
  396. msg("سبد های شرکت کنندگان در مسابقه دریافت شد.");
  397. } catch (HeadlessException | IOException | SQLException | NumberFormatException e) {
  398. err(e);
  399. }
  400. }
  401.  
  402. private void btn_get_buy_pricesActionPerformed(java.awt.event.ActionEvent evt) {
  403. try {
  404. jFileChooser1 = new javax.swing.JFileChooser();
  405. jFileChooser1.setCurrentDirectory(new File("C:\\Users\\Mohammad\\Desktop"));
  406. int result = jFileChooser1.showOpenDialog(this);
  407. if (result == JFileChooser.CANCEL_OPTION) {
  408. return;
  409. }
  410. mhjfile = jFileChooser1.getSelectedFile();
  411. mhjworkbook = new XSSFWorkbook(mhjfile);
  412. mhjsheet = mhjworkbook.getSheetAt(0);
  413. XSSFDataFormat format = mhjworkbook.createDataFormat();
  414. con = mhjexcel.con;
  415. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  416. sql = "SELECT * FROM mosabegheh";
  417. rs = stmt.executeQuery(sql);
  418. firstrow = 3;
  419. firstcol = 1;//تغییر داده نشود
  420. lastcol = 5;
  421. int tickerscol = 0;//در اکسل
  422. int pricecol = 10;//در اکسل
  423.  
  424. // int firstticker = 1;
  425. // int finalticker = 4;
  426. rs.beforeFirst();
  427. while (rs.next()) {
  428. for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
  429. String ticker = rs.getString("portfolioname" + String.valueOf(tickernumber));
  430. if (ticker == null || ticker.equals("")) {
  431. continue;
  432. }
  433. for (rowindex = firstrow;; rowindex++) {
  434. mhjrow = mhjsheet.getRow(rowindex);
  435. if (mhjrow == null) {
  436. boolean found = false;
  437. for (int i = 0; i < manual_tickers_names.length; i++) {
  438. String name = manual_tickers_names[i];
  439. double buy = manual_tickers_buys[i];
  440. if (name != null && name.equals(ticker)) {
  441. rs.updateDouble("buyprice" + String.valueOf(tickernumber), buy);
  442. rs.updateRow();
  443. found = true;
  444. break;
  445. }
  446. }
  447. if (!found) {
  448. out("قیمت خرید "
  449. + ticker
  450. + " پیدا نشد"
  451. );
  452. }
  453. break;
  454. }
  455. mhjcell = mhjrow.getCell(tickerscol);
  456. cellcontents = mhjdataformater.formatCellValue(mhjcell);
  457. cellcontents = cellcontents.replaceAll(",", "");
  458. cellcontents = cellcontents.replaceAll("\\s", "");
  459. cellcontents = cellcontents.replaceAll("-", "_");
  460. cellcontents = cellcontents.replaceAll("\\.", "_");
  461. cellcontents = cellcontents.replaceAll("ك", "ک");
  462. cellcontents = cellcontents.replaceAll("ي", "ی");
  463.  
  464. cellcontents = cellcontents.replaceAll("آ", "ا");
  465.  
  466. if (cellcontents.equals(ticker)) {
  467. // out(ticker + " پیدا شد در سطر شماره ی " + rowindex);
  468. mhjcell = mhjrow.getCell(pricecol);
  469. cellcontents = mhjdataformater.formatCellValue(mhjcell);
  470. Double price = Double.parseDouble(cellcontents);
  471. // out("price = " + price);
  472. rs.updateDouble("buyprice" + String.valueOf(tickernumber), price);
  473. rs.updateRow();
  474. break;
  475. }
  476. }
  477. }
  478. }
  479. rs.close();
  480. stmt.close();
  481. msg("قیمت های اولیه دریافت شد");
  482. } catch (HeadlessException | IOException | InvalidFormatException | SQLException | NumberFormatException e) {
  483. err(e);
  484. }
  485. }
  486.  
  487. private void btn_get_sell_pricesActionPerformed(java.awt.event.ActionEvent evt) {
  488. try {
  489. jFileChooser1 = new javax.swing.JFileChooser();
  490. jFileChooser1.setCurrentDirectory(new File("C:\\Users\\Mohammad\\Desktop"));
  491. int result = jFileChooser1.showOpenDialog(this);
  492. if (result == JFileChooser.CANCEL_OPTION) {
  493. return;
  494. }
  495. mhjfile = jFileChooser1.getSelectedFile();
  496. mhjworkbook = new XSSFWorkbook(mhjfile);
  497. mhjsheet = mhjworkbook.getSheetAt(0);
  498. XSSFDataFormat format = mhjworkbook.createDataFormat();
  499. con = mhjexcel.con;
  500. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  501. sql = "SELECT * FROM mosabegheh";
  502. rs = stmt.executeQuery(sql);
  503. firstrow = 3;
  504. firstcol = 1;//تغییر داده نشود
  505. lastcol = 5;
  506. int tickerscol = 0;//در اکسل
  507. int pricecol = 10;//در اکسل
  508.  
  509. // int firstticker = 1;
  510. // int finalticker = 4;//numberoftickers
  511. rs.beforeFirst();
  512. while (rs.next()) {
  513. for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
  514. String ticker = rs.getString("portfolioname" + String.valueOf(tickernumber));
  515. if (ticker == null || ticker.equals("")) {
  516. continue;
  517. }
  518. for (rowindex = firstrow;; rowindex++) {
  519. mhjrow = mhjsheet.getRow(rowindex);
  520.  
  521. if (mhjrow == null) {
  522. boolean found = false;
  523. for (int i = 0; i < manual_tickers_names.length; i++) {
  524. String name = manual_tickers_names[i];
  525. double sell = manual_tickers_sells[i];
  526. if (name != null && name.equals(ticker)) {
  527. rs.updateDouble("sellprice" + String.valueOf(tickernumber), sell);
  528. rs.updateRow();
  529. found = true;
  530. break;
  531. }
  532. }
  533. if (!found) {
  534. out("قیمت فروش "
  535. + ticker
  536. + " پیدا نشد"
  537. );
  538. }
  539. break;
  540. }
  541.  
  542. mhjcell = mhjrow.getCell(tickerscol);
  543. cellcontents = mhjdataformater.formatCellValue(mhjcell);
  544. cellcontents = cellcontents.replaceAll(",", "");
  545. cellcontents = cellcontents.replaceAll("\\s", "");
  546. cellcontents = cellcontents.replaceAll("_", "");
  547. cellcontents = cellcontents.replaceAll("-", "_");
  548. cellcontents = cellcontents.replaceAll("\\.", "_");
  549. cellcontents = cellcontents.replaceAll("ك", "ک");
  550. cellcontents = cellcontents.replaceAll("ي", "ی");
  551.  
  552. cellcontents = cellcontents.replaceAll("آ", "ا");
  553.  
  554. if (cellcontents.equals(ticker)) {
  555. // out(ticker + " پیدا شد در سطر شماره ی " + rowindex);
  556. mhjcell = mhjrow.getCell(pricecol);
  557. cellcontents = mhjdataformater.formatCellValue(mhjcell);
  558. Double price = Double.parseDouble(cellcontents);
  559. // out("price = " + price);
  560. rs.updateDouble("sellprice" + String.valueOf(tickernumber), price);
  561. rs.updateRow();
  562. break;
  563. }
  564. }
  565. }
  566. }
  567. rs.close();
  568. stmt.close();
  569. msg("قیمت های امروز دریافت شد.");
  570. } catch (HeadlessException | IOException | InvalidFormatException | SQLException | NumberFormatException e) {
  571. err(e);
  572. }
  573. }
  574.  
  575. private void setTableRowNumbers(String tablename) {
  576. try {
  577. con = mhjexcel.con;
  578. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  579. sql = "SELECT * FROM " + tablename; //lets give it from search
  580. rs = stmt.executeQuery(sql);
  581. rs.beforeFirst();
  582. while (rs.next()) {
  583. rs.updateInt("rownumber", rs.getRow());
  584. rs.updateRow();
  585. }
  586. out("در جدول " + tablename + "به میزان " + rs.getRow() + "عدد سطر شماره گذاری شد");
  587. rs.close();
  588. stmt.close();
  589.  
  590. } catch (Exception e) {
  591. err(e);
  592. }
  593. }
  594.  
  595. private void write2ExcelOld() {
  596. try {
  597. int number_of_tickers = Integer.parseInt(txt_number_of_tickers.getText());
  598. // con = mhjexcel.con;
  599. // stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  600. // sql = "SELECT * FROM mosabegheh order by profit_person desc";
  601. // rs = stmt.executeQuery(sql);
  602. // //___________________________________________
  603. // mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\نتایج.xlsx");
  604. // mhjfileoutput = new FileOutputStream(mhjfile);
  605. // mhjworkbook = new XSSFWorkbook();
  606. mhjsheet = mhjworkbook.createSheet("مدل 1");//mhjsheet = mhjworkbook.getSheetAt(sheetindex);//workbook exist from before
  607. //______________________styles_____________________
  608.  
  609. DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###.##");
  610.  
  611. CellStyle styleHeaders = mhjworkbook.createCellStyle();
  612. styleHeaders.setAlignment(HorizontalAlignment.CENTER);
  613. styleHeaders.setBorderBottom(BorderStyle.THIN);
  614. styleHeaders.setBorderLeft(BorderStyle.THIN);
  615. styleHeaders.setBorderRight(BorderStyle.THIN);
  616. styleHeaders.setBorderTop(BorderStyle.THIN);
  617. styleHeaders.setBottomBorderColor(IndexedColors.WHITE.getIndex());
  618. styleHeaders.setRightBorderColor(IndexedColors.WHITE.getIndex());
  619. styleHeaders.setLeftBorderColor(IndexedColors.WHITE.getIndex());
  620. styleHeaders.setTopBorderColor(IndexedColors.WHITE.getIndex());
  621. Font f = mhjworkbook.createFont();
  622. f.setFontHeightInPoints((short) 12);//set font 1 to 12 point type
  623. f.setColor((short) 0xc);//COLOR_RED
  624. f.setBold(true);
  625. styleHeaders.setFont(f);
  626. f.setColor(IndexedColors.WHITE.getIndex());
  627. styleHeaders.setFont(f);
  628. styleHeaders.setFillForegroundColor(IndexedColors.BLUE.getIndex());
  629. styleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  630.  
  631. CellStyle styleNormal = mhjworkbook.createCellStyle();
  632. styleNormal.setAlignment(HorizontalAlignment.CENTER);
  633. styleNormal.setBorderBottom(BorderStyle.THIN);
  634. styleNormal.setBorderLeft(BorderStyle.THIN);
  635. styleNormal.setBorderRight(BorderStyle.THIN);
  636. styleNormal.setBorderTop(BorderStyle.THIN);
  637.  
  638. CellStyle styleTickers = mhjworkbook.createCellStyle();
  639. styleTickers.setAlignment(HorizontalAlignment.CENTER);
  640. styleTickers.setBorderBottom(BorderStyle.THIN);
  641. styleTickers.setBorderLeft(BorderStyle.THIN);
  642. styleTickers.setBorderRight(BorderStyle.THIN);
  643. styleTickers.setBorderTop(BorderStyle.THIN);
  644. styleTickers.setFillForegroundColor(IndexedColors.AQUA.getIndex());
  645. styleTickers.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  646.  
  647. //___________________________________________
  648. for (int i = 0; i <= number_of_tickers + 1; i++) {
  649. mhjsheet.setColumnWidth((short) i, 4000);
  650. }
  651. //___________________________________________
  652. // mhjworkbook.setSheetName(0, "نتایج");
  653. mhjrow = mhjsheet.createRow(0);
  654. // mhjcell = mhjrow.createCell(0);
  655. // mhjcell.setCellValue("نتایج مسابقه");
  656. // mhjcell.setCellStyle(style1);
  657. mhjcell = mhjrow.createCell(0);
  658. mhjcell.setCellValue("شرکت کننده");
  659. mhjcell.setCellStyle(styleHeaders);
  660. for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
  661. mhjcell = mhjrow.createCell(tickernumber);
  662. mhjcell.setCellValue("نماد " + String.valueOf(tickernumber));
  663. mhjcell.setCellStyle(styleHeaders);
  664. }
  665. //___________________________________________
  666. rs.beforeFirst();
  667. while (rs.next()) {
  668. rowindex = rs.getRow() * 2 - 1;
  669. mhjrow = mhjsheet.createRow(rowindex);
  670. Row headerRow = mhjsheet.getRow(0);
  671. headerRow.setHeight((short) 0x249);//mhjrow.setHeightInPoints(25f);
  672. // mhjcell = mhjrow.createCell(0);
  673. // mhjcell.setCellValue("سبد");
  674. mhjcell = mhjrow.createCell(0);
  675. mhjcell.setCellValue(rs.getString("person"));
  676. mhjcell.setCellStyle(styleHeaders);
  677. for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
  678. mhjcell = mhjrow.createCell(tickernumber);
  679. mhjcell.setCellValue(rs.getString("portfolioname" + String.valueOf(tickernumber)));
  680. mhjcell.setCellStyle(styleTickers);
  681. }
  682. //_____________
  683. rowindex++;
  684. mhjrow = mhjsheet.createRow(rowindex);
  685. // mhjcell = mhjrow.createCell(0);
  686. // mhjcell.setCellValue("درصد");
  687. mhjcell = mhjrow.createCell(0);
  688. mhjcell.setCellValue(Double.parseDouble(floatformatter.format(rs.getDouble("profit_person"))));
  689. mhjcell.setCellStyle(styleNormal);
  690. for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
  691. mhjcell = mhjrow.createCell(tickernumber);
  692. mhjcell.setCellStyle(styleNormal);
  693. Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
  694. if (rs.wasNull()) {
  695. continue;
  696. }
  697. mhjcell.setCellValue(Double.parseDouble(floatformatter.format(thisprofit)));
  698. }
  699. }
  700. // XSSFPivotTable pivotTable = mhjsheet.createPivotTable(new AreaReference(new CellReference("a1"), new CellReference("c7")), new CellReference("a1"));
  701. // pivotTable.addRowLabel(0);
  702. // XSSFTable my_table = mhjsheet.createTable();
  703. // CTTable cttable = my_table.getCTTable();
  704. // AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(rowindex, numberoftickers + 1));
  705. // cttable.setRef(my_data_range.formatAsString());
  706. // cttable.setTotalsRowCount(rowindex+1);
  707. // cttable.setDisplayName("نتایج مسابقه");
  708. // cttable.setName("match_results");
  709. // cttable.setId(1L);
  710. // cttable.addNewTableColumns();
  711. // CTTableColumns columns = cttable.addNewTableColumns();
  712. // columns.setCount(5L); //define number of columns
  713. // for (int i = 0; i < 5; i++) {
  714. // CTTableColumn column = columns.addNewTableColumn();
  715. // column.setName("Column" + i);
  716. // column.setId(i + 1);
  717. // }
  718. //_________________________________________________
  719. // mhjworkbook.write(mhjfileoutput);
  720. // mhjworkbook.close();
  721. // mhjfileoutput.close();
  722. // stmt.close();
  723. // rs.close();
  724. // if (mhjfile.exists() || mhjfile.isDirectory()) {
  725. // Desktop.getDesktop().open(mhjfile);
  726. //// Runtime.getRuntime().exec("explorer.exe /select , " + mhjfile);
  727. // }
  728. // _____________________ تعیین پهنای ستون ها _____________________________
  729. // for (int i = 0; i < HeaderNames.length; i++) {
  730. // mhjsheet.setColumnWidth((short) i, ColWidth[i]);
  731. // }
  732. for (int columnNumber = 0; columnNumber <= number_of_tickers; columnNumber++) {
  733. mhjsheet.autoSizeColumn(columnNumber, true);
  734. }
  735. } catch (SQLException | NumberFormatException e) {
  736. err(e);
  737. }
  738. }
  739.  
  740. private void write2ExcelResults() {
  741. try {
  742. int number_of_tickers = Integer.parseInt(txt_number_of_tickers.getText());
  743. // con = mhjexcel.con;
  744. // stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  745. // sql = "SELECT * FROM mosabegheh order by profit_person desc";
  746. // rs = stmt.executeQuery(sql);
  747. // //___________________________________________
  748. // mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\نتایج.xlsx");
  749. // mhjfileoutput = new FileOutputStream(mhjfile);
  750. // mhjworkbook = new XSSFWorkbook();
  751. mhjsheet = mhjworkbook.createSheet("نتایج");//mhjsheet = mhjworkbook.getSheetAt(sheetindex);//workbook exist from before
  752. //______________________styles_____________________
  753. // mhjsheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).setRightToLeft(true);
  754.  
  755. DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###.##");
  756.  
  757. CellStyle styleHeaders = mhjworkbook.createCellStyle();
  758. styleHeaders.setAlignment(HorizontalAlignment.CENTER);
  759. styleHeaders.setVerticalAlignment(styleHeaders.VERTICAL_CENTER);
  760. styleHeaders.setBorderBottom(BorderStyle.THIN);
  761. styleHeaders.setBorderLeft(BorderStyle.THIN);
  762. styleHeaders.setBorderRight(BorderStyle.THIN);
  763. styleHeaders.setBorderTop(BorderStyle.THIN);
  764. styleHeaders.setBottomBorderColor(IndexedColors.WHITE.getIndex());
  765. styleHeaders.setRightBorderColor(IndexedColors.WHITE.getIndex());
  766. styleHeaders.setLeftBorderColor(IndexedColors.WHITE.getIndex());
  767. styleHeaders.setTopBorderColor(IndexedColors.WHITE.getIndex());
  768. Font f = mhjworkbook.createFont();
  769. f.setFontHeightInPoints((short) 12);//set font 1 to 12 point type
  770. f.setColor((short) 0xc);//COLOR_RED
  771. f.setBold(true);
  772. styleHeaders.setFont(f);
  773. f.setColor(IndexedColors.WHITE.getIndex());
  774. styleHeaders.setFont(f);
  775. styleHeaders.setFillForegroundColor(IndexedColors.BLUE.getIndex());
  776. styleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  777.  
  778. CellStyle styleHeaders2 = styleHeaders;
  779. styleHeaders2.setAlignment(HorizontalAlignment.RIGHT);
  780.  
  781. CellStyle styleNormal = mhjworkbook.createCellStyle();
  782. styleNormal.setAlignment(HorizontalAlignment.CENTER);
  783. styleNormal.setBorderBottom(BorderStyle.THIN);
  784. styleNormal.setBorderLeft(BorderStyle.THIN);
  785. styleNormal.setBorderRight(BorderStyle.THIN);
  786. styleNormal.setBorderTop(BorderStyle.THIN);
  787.  
  788. CellStyle styleTickers = mhjworkbook.createCellStyle();
  789. styleTickers.setAlignment(HorizontalAlignment.CENTER);
  790. styleHeaders.setVerticalAlignment(styleHeaders.VERTICAL_CENTER);
  791. styleTickers.setBorderBottom(BorderStyle.THIN);
  792. styleTickers.setBorderLeft(BorderStyle.THIN);
  793. styleTickers.setBorderRight(BorderStyle.THIN);
  794. styleTickers.setBorderTop(BorderStyle.THIN);
  795. styleTickers.setFillForegroundColor(IndexedColors.AQUA.getIndex());
  796. styleTickers.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  797.  
  798. CellStyle styleToogle = mhjworkbook.createCellStyle();
  799.  
  800. for (int i = 0; i <= number_of_tickers + 1; i++) {
  801. mhjsheet.setColumnWidth((short) i, 6000);
  802. }
  803. //____________________هدر های جدول اکسل_______________________
  804. // mhjworkbook.setSheetName(0, "نتایج");
  805. mhjrow = mhjsheet.createRow(0);
  806. mhjcell = mhjrow.createCell(0);
  807. mhjcell.setCellValue("شرکت کننده");
  808. mhjcell.setCellStyle(styleHeaders);
  809. for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
  810. mhjcell = mhjrow.createCell(tickernumber);
  811. mhjcell.setCellValue("نماد " + String.valueOf(tickernumber));
  812. mhjcell.setCellStyle(styleHeaders);
  813. }
  814. //___________________________________________
  815. boolean oddcounter = true;
  816. rs.beforeFirst();
  817. while (rs.next()) {
  818. if (oddcounter) {
  819. styleToogle = styleNormal;
  820. } else {
  821. styleToogle = styleTickers;
  822. }
  823. oddcounter = !oddcounter;
  824. rowindex = rs.getRow();
  825. mhjrow = mhjsheet.createRow(rowindex);
  826. Row headerRow = mhjsheet.getRow(0);
  827. headerRow.setHeight((short) 0x249);
  828. mhjcell = mhjrow.createCell(0);
  829. mhjcell.setCellStyle(styleHeaders2);
  830. String Person = rs.getString("person");
  831. Double Person_profit = rs.getDouble("profit_person");
  832. // mhjcell.setCellValue(Person + " - " + floatformatter.format(Person_profit));
  833. mhjcell.setCellValue("\u200E" + Person + " (" + floatformatter.format(Person_profit) + ")");
  834. for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
  835. mhjcell = mhjrow.createCell(tickernumber);
  836. mhjcell.setCellStyle(styleToogle);
  837. Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
  838. String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
  839. if (rs.wasNull()) {
  840. continue;
  841. }
  842. // mhjcell.setCellValue(tickername + " - " + floatformatter.format(thisprofit));
  843. mhjcell.setCellValue("\u200E" + tickername + " (" + floatformatter.format(thisprofit) + ")");
  844. if (thisprofit == unknownvalue) {
  845. mhjcell.setCellValue("\u200E" + tickername + " (؟)");
  846. }
  847. }
  848. }
  849. // //_________________________________________________
  850. // mhjworkbook.write(mhjfileoutput);
  851. // mhjworkbook.close();
  852. // mhjfileoutput.close();
  853. // stmt.close();
  854. // rs.close();
  855. // if (mhjfile.exists() || mhjfile.isDirectory()) {
  856. // Desktop.getDesktop().open(mhjfile);
  857. // }
  858. // _____________________ تعیین پهنای ستون ها _____________________________
  859. // for (int i = 0; i < HeaderNames.length; i++) {
  860. // mhjsheet.setColumnWidth((short) i, ColWidth[i]);
  861. // }
  862. for (int columnNumber = 0; columnNumber <= number_of_tickers; columnNumber++) {
  863. mhjsheet.autoSizeColumn(columnNumber, true);
  864. }
  865. } catch (Exception e) {
  866. err(e);
  867. }
  868. }
  869.  
  870. private void write2ExcelPortfolios() {
  871. try {
  872. sql = "select * from mosabegheh order by rownumber"; // می خواهیم ترتیب بر اساس وارد شدن سبد ها باشد
  873. rs = stmt.executeQuery(sql);
  874. // _______________________ پارامتر ها _______________
  875. mhjsheet = mhjworkbook.createSheet("سبد ها");//mhjsheet = mhjworkbook.getSheetAt(sheetindex);//workbook exist from before
  876.  
  877. rs.last();
  878. int participantsCount = rs.getRow();
  879.  
  880. int totalheaderrows = 1;
  881. int rowheader = 0;
  882. int rowdetails = 1;
  883.  
  884. int totalsubrows = 3;
  885. int subrowticker = 0;
  886. int subrowbuy = 1;
  887. int subrowsell = 2;
  888.  
  889. int totalcols = 5;
  890. int colportfolionumber = 0;
  891. int colparticipant = 1;
  892. int colrank = 2;
  893. int colprofit = 3;
  894. int colkind = 4;
  895.  
  896. int numOfTickers = Integer.parseInt(txt_number_of_tickers.getText());
  897.  
  898. String HeaderNames[] = new String[totalcols + numOfTickers];
  899. HeaderNames[0] = "شماره سبد";
  900. HeaderNames[1] = "شرکت کننده";
  901. HeaderNames[2] = "رتبه";
  902. HeaderNames[3] = "سود";
  903. HeaderNames[4] = "نوع";
  904. for (int tickernumber = firstticker; tickernumber <= numOfTickers; tickernumber++) {
  905. HeaderNames[4 + tickernumber] = "نماد " + tickernumber;
  906. }
  907.  
  908. // int ColWidth[] = new int[totalcols + numberoftickers];
  909. // ColWidth[0] = 2500;
  910. // ColWidth[1] = 6000;
  911. // ColWidth[2] = 1000;
  912. // ColWidth[3] = 2000;
  913. // ColWidth[4] = 6000;
  914. // for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
  915. // ColWidth[4 + tickernumber] = 6000;
  916. // }
  917. // _____________________ تولید سطر ها و ستون ها _______________
  918. for (int i = 0; i <= (participantsCount * totalsubrows); i++) { // participantsCount + 1 -1 // one for header row and one for begining of participatns from 1.
  919. mhjrow = mhjsheet.createRow(i);
  920. for (int j = 0; j < ((totalcols + numOfTickers)); j++) {
  921. mhjcell = mhjrow.createCell(j);
  922. }
  923. }
  924. //______________________styles_____________________
  925. // mhjsheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).setRightToLeft(true);
  926.  
  927. DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###.##");
  928.  
  929. CellStyle styleHeaders = mhjworkbook.createCellStyle();
  930. styleHeaders.setAlignment(HorizontalAlignment.CENTER);
  931. styleHeaders.setVerticalAlignment(styleHeaders.VERTICAL_CENTER);
  932. styleHeaders.setBorderBottom(BorderStyle.THIN);
  933. styleHeaders.setBorderLeft(BorderStyle.THIN);
  934. styleHeaders.setBorderRight(BorderStyle.THIN);
  935. styleHeaders.setBorderTop(BorderStyle.THIN);
  936. styleHeaders.setBottomBorderColor(IndexedColors.WHITE.getIndex());
  937. styleHeaders.setRightBorderColor(IndexedColors.WHITE.getIndex());
  938. styleHeaders.setLeftBorderColor(IndexedColors.WHITE.getIndex());
  939. styleHeaders.setTopBorderColor(IndexedColors.WHITE.getIndex());
  940. Font f = mhjworkbook.createFont();
  941. f.setFontHeightInPoints((short) 12);//set font 1 to 12 point type
  942. f.setColor((short) 0xc);//COLOR_RED
  943. f.setBold(true);
  944. styleHeaders.setFont(f);
  945. f.setColor(IndexedColors.WHITE.getIndex());
  946. styleHeaders.setFont(f);
  947. styleHeaders.setFillForegroundColor(IndexedColors.BLUE.getIndex());
  948. styleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  949.  
  950. CellStyle styleNormal = mhjworkbook.createCellStyle();
  951. styleNormal.setAlignment(HorizontalAlignment.CENTER);
  952. styleNormal.setVerticalAlignment(styleNormal.VERTICAL_CENTER);
  953. styleNormal.setBorderBottom(BorderStyle.THIN);
  954. styleNormal.setBorderLeft(BorderStyle.THIN);
  955. styleNormal.setBorderRight(BorderStyle.THIN);
  956. styleNormal.setBorderTop(BorderStyle.THIN);
  957.  
  958. CellStyle styleTickers = mhjworkbook.createCellStyle();
  959. styleTickers.setAlignment(HorizontalAlignment.CENTER);
  960. styleTickers.setVerticalAlignment(styleTickers.VERTICAL_CENTER);
  961. styleTickers.setBorderBottom(BorderStyle.THIN);
  962. styleTickers.setBorderLeft(BorderStyle.THIN);
  963. styleTickers.setBorderRight(BorderStyle.THIN);
  964. styleTickers.setBorderTop(BorderStyle.THIN);
  965. styleTickers.setFillForegroundColor(IndexedColors.AQUA.getIndex());
  966. styleTickers.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  967.  
  968. CellStyle styleToogle = mhjworkbook.createCellStyle();
  969. //________________________هدر های اکسل ___________________
  970. mhjrow = mhjsheet.getRow(rowheader);
  971.  
  972. for (int i = 0; i < HeaderNames.length; i++) {
  973. mhjcell = mhjrow.getCell(i);
  974. mhjcell.setCellValue(HeaderNames[i]);
  975. mhjcell.setCellStyle(styleHeaders);
  976. // mhjsheet.setColumnWidth((short) i, ColWidth[i]);
  977. }
  978. Row headerRow = mhjsheet.getRow(rowheader);
  979. headerRow.setHeight((short) 0x249);
  980.  
  981. //______________________سایر سطر ها_____________________
  982. boolean oddcounter = true;
  983. rs.beforeFirst();
  984. while (rs.next()) {
  985. if (oddcounter) {
  986. styleToogle = styleNormal;
  987. } else {
  988. styleToogle = styleTickers;
  989. }
  990. oddcounter = !oddcounter;
  991. //______________________ ستون شماره سبد _____________________
  992. rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails;
  993. mhjrow = mhjsheet.getRow(rowindex);
  994. mhjcell = mhjrow.getCell(colportfolionumber);
  995. mhjcell.setCellStyle(styleHeaders);
  996. mhjcell.setCellValue(rs.getInt("rownumber"));
  997. mhjrow = mhjsheet.getRow(rowindex + 1);
  998. mhjcell = mhjrow.getCell(colportfolionumber);
  999. mhjcell.setCellStyle(styleHeaders);
  1000. // mhjcell.setCellValue("_");
  1001. mhjrow = mhjsheet.getRow(rowindex + 2);
  1002. mhjcell = mhjrow.getCell(colportfolionumber);
  1003. mhjcell.setCellStyle(styleHeaders);
  1004. // mhjcell.setCellValue("_");
  1005. if (!chk_dont_merge.isSelected()) {
  1006. mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colportfolionumber, colportfolionumber));
  1007. }
  1008. //______________________ ستون رتبه _____________________
  1009. rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails;
  1010. mhjrow = mhjsheet.getRow(rowindex);
  1011. mhjcell = mhjrow.getCell(colrank);
  1012. mhjcell.setCellStyle(styleHeaders);
  1013. mhjcell.setCellValue(rs.getInt("rank"));
  1014. mhjrow = mhjsheet.getRow(rowindex + 1);
  1015. mhjcell = mhjrow.getCell(colrank);
  1016. mhjcell.setCellStyle(styleHeaders);
  1017. // mhjcell.setCellValue("_");
  1018. mhjrow = mhjsheet.getRow(rowindex + 2);
  1019. mhjcell = mhjrow.getCell(colrank);
  1020. mhjcell.setCellStyle(styleHeaders);
  1021. // mhjcell.setCellValue("_");
  1022. if (!chk_dont_merge.isSelected()) {
  1023. mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colrank, colrank));
  1024. }
  1025. //______________________ ستون سود _____________________
  1026. rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails; //یکی به خاطر اینکه پایگاه داده از یک شروع میشه و این از صفر یکی هم به خاطر اینکه از بین سه تا بیاد وسط
  1027. mhjrow = mhjsheet.getRow(rowindex);
  1028. mhjcell = mhjrow.getCell(colprofit);
  1029. mhjcell.setCellStyle(styleHeaders);
  1030. Double Person_profit = rs.getDouble("profit_person");
  1031. mhjcell.setCellValue(Double.parseDouble(floatformatter.format(Person_profit)));
  1032. mhjrow = mhjsheet.getRow(rowindex + 1);
  1033. mhjcell = mhjrow.getCell(colprofit);
  1034. mhjcell.setCellStyle(styleHeaders);
  1035. // mhjcell.setCellValue("_");
  1036. mhjrow = mhjsheet.getRow(rowindex + 2);
  1037. mhjcell = mhjrow.getCell(colprofit);
  1038. mhjcell.setCellStyle(styleHeaders);
  1039. // mhjcell.setCellValue("_");
  1040. if (!chk_dont_merge.isSelected()) {
  1041. mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colprofit, colprofit));
  1042. }
  1043. //______________________ ستون شرکت کنندگان _____________________
  1044. rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails; //یکی به خاطر اینکه پایگاه داده از یک شروع میشه و این از صفر یکی هم به خاطر اینکه از بین سه تا بیاد وسط
  1045. mhjrow = mhjsheet.getRow(rowindex);
  1046. mhjcell = mhjrow.getCell(colparticipant);
  1047. mhjcell.setCellStyle(styleHeaders);
  1048. String Person = rs.getString("person");
  1049. mhjcell.setCellValue("\u200E" + Person);
  1050. mhjrow = mhjsheet.getRow(rowindex + 1);
  1051. mhjcell = mhjrow.getCell(colparticipant);
  1052. mhjcell.setCellStyle(styleHeaders);
  1053. mhjrow = mhjsheet.getRow(rowindex + 2);
  1054. mhjcell = mhjrow.getCell(colparticipant);
  1055. mhjcell.setCellStyle(styleHeaders);
  1056. if (!chk_dont_merge.isSelected()) {
  1057. mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colparticipant, colparticipant));
  1058. }
  1059. //_____________________ ستون نوع عمل _________________________
  1060. mhjrow = mhjsheet.getRow(rowindex + subrowticker);
  1061. mhjcell = mhjrow.getCell(colkind);
  1062. mhjcell.setCellStyle(styleToogle);
  1063. mhjcell.setCellValue("نماد");
  1064. mhjrow = mhjsheet.getRow(rowindex + subrowbuy);
  1065. mhjcell = mhjrow.getCell(colkind);
  1066. mhjcell.setCellStyle(styleToogle);
  1067. mhjcell.setCellValue("خرید");
  1068. mhjrow = mhjsheet.getRow(rowindex + subrowsell);
  1069. mhjcell = mhjrow.getCell(colkind);
  1070. mhjcell.setCellStyle(styleToogle);
  1071. mhjcell.setCellValue("فروش");
  1072. //_____________________ ستون نوع عمل _________________________
  1073. // _____________________ اول نماد _____________________________
  1074. mhjrow = mhjsheet.getRow(rowindex + subrowticker);
  1075. for (int tickernumber = 1; tickernumber <= numOfTickers; tickernumber++) {
  1076. mhjcell = mhjrow.getCell(tickernumber + colkind);
  1077. mhjcell.setCellStyle(styleToogle);
  1078. Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
  1079. String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
  1080. if (rs.wasNull()) {
  1081. continue;
  1082. }
  1083. mhjcell.setCellValue("\u200E" + tickername + " (" + floatformatter.format(thisprofit) + ")");
  1084. if (thisprofit == unknownvalue) {
  1085. mhjcell.setCellValue("\u200E" + tickername + " (؟)");
  1086. }
  1087. }
  1088. // _____________________ دوم خرید _____________________________
  1089. mhjrow = mhjsheet.getRow(rowindex + subrowbuy);
  1090. for (int tickernumber = 1; tickernumber <= numOfTickers; tickernumber++) {
  1091. mhjcell = mhjrow.getCell(tickernumber + colkind);
  1092. mhjcell.setCellStyle(styleToogle);
  1093. Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
  1094. String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
  1095. if (rs.wasNull()) {
  1096. continue;
  1097. }
  1098. double buy = rs.getDouble("buyprice" + String.valueOf(tickernumber));
  1099. mhjcell.setCellValue(buy);
  1100. if (buy == 0) {
  1101. mhjcell.setCellValue("نامعلوم");
  1102. }
  1103. }
  1104. // _____________________ سوم فروش _____________________________
  1105. mhjrow = mhjsheet.getRow(rowindex + subrowsell);
  1106. for (int tickernumber = 1; tickernumber <= numOfTickers; tickernumber++) {
  1107. mhjcell = mhjrow.getCell(tickernumber + colkind);
  1108. mhjcell.setCellStyle(styleToogle);
  1109. Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
  1110. String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
  1111. if (rs.wasNull()) {
  1112. continue;
  1113. }
  1114. double sell = rs.getDouble("sellprice" + String.valueOf(tickernumber));
  1115. mhjcell.setCellValue(sell);
  1116. if (sell == 0) {
  1117. mhjcell.setCellValue("نامعلوم");
  1118. }
  1119. }
  1120. }
  1121. // _____________________ تعیین پهنای ستون ها _____________________________
  1122. // for (int i = 0; i < HeaderNames.length; i++) {
  1123. // mhjsheet.setColumnWidth((short) i, ColWidth[i]);
  1124. // }
  1125. for (int columnNumber = 0; columnNumber <= totalcols + numOfTickers; columnNumber++) {
  1126. mhjsheet.autoSizeColumn(columnNumber, true);
  1127. }
  1128. } catch (SQLException | NumberFormatException e) {
  1129. err(e);
  1130. }
  1131. }
  1132.  
  1133. private void btnoutputActionPerformed(java.awt.event.ActionEvent evt) {
  1134. try {
  1135. //___________________________________________
  1136. con = mhjexcel.con;
  1137. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  1138. sql = "SELECT * FROM mosabegheh order by profit_person desc";
  1139. rs = stmt.executeQuery(sql);
  1140. //___________________________________________
  1141. mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\نتایج.xlsx");
  1142. mhjfileoutput = new FileOutputStream(mhjfile);
  1143. mhjworkbook = new XSSFWorkbook();
  1144. // if (radio1.isSelected()) {
  1145. // write2Excel1();
  1146. // } else if (radio2.isSelected()) {
  1147. // write2Excel2();
  1148. // } else if (radio3.isSelected()) {
  1149. // write2Excel3();
  1150. // }
  1151. write2ExcelResults();
  1152. out("نتایج تولید شد.");
  1153. write2ExcelPortfolios();
  1154. out("سبد ها تولید شد.");
  1155. write2ExcelOld();
  1156. out("مدل اول تولید شد.");
  1157. //_________________________________________________
  1158. mhjworkbook.write(mhjfileoutput);
  1159. mhjworkbook.close();
  1160. mhjfileoutput.close();
  1161. stmt.close();
  1162. rs.close();
  1163. if (mhjfile.exists() || mhjfile.isDirectory()) {
  1164. Desktop.getDesktop().open(mhjfile);
  1165. }
  1166. } catch (SQLException | IOException e) {
  1167. err(e);
  1168. }
  1169. }
  1170.  
  1171. private void btncomputationActionPerformed(java.awt.event.ActionEvent evt) {
  1172. try {
  1173. // ___________________ numbering rows of database (in table mosabegheh)
  1174. setTableRowNumbers("mosabegheh");
  1175. // ___________________ computation of profits
  1176. con = mhjexcel.con;
  1177. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  1178. sql = "SELECT * FROM mosabegheh";
  1179. rs = stmt.executeQuery(sql);
  1180. double number_of_persons = 0;
  1181.  
  1182. double max_portfolios_tickers = 0;
  1183.  
  1184. rs.beforeFirst();
  1185. while (rs.next()) {
  1186. double profitcum = 0;
  1187. double portfolio_number = 0;
  1188. for (int tickernumber = 1; tickernumber <= numberoftickers; tickernumber++) {
  1189. String ticker = rs.getString("portfolioname" + String.valueOf(tickernumber));
  1190. if (ticker != null) {
  1191. double buy = rs.getDouble("buyprice" + String.valueOf(tickernumber));
  1192. if (buy == 0) {
  1193. out("مشکل در قیمت خرید " + rs.getString("person") + " profit" + String.valueOf(tickernumber));
  1194. rs.updateDouble("profit" + String.valueOf(tickernumber), unknownvalue);
  1195. continue;
  1196. }
  1197. double sell = rs.getDouble("sellprice" + String.valueOf(tickernumber));
  1198. if (sell == 0) {
  1199. out("مشکل در قیمت فروش " + rs.getString("person") + " profit" + String.valueOf(tickernumber));
  1200. rs.updateDouble("profit" + String.valueOf(tickernumber), unknownvalue);
  1201. continue;
  1202. }
  1203. portfolio_number++;
  1204. double profit = sell - buy;
  1205. double profitpercents = 100 * profit / buy;
  1206. rs.updateDouble("profit" + String.valueOf(tickernumber), profitpercents);
  1207. profitcum += profitpercents;
  1208. }
  1209. }
  1210. // out(profitcum);
  1211. // out(portfolio_number);
  1212. double avgprofit = 0;
  1213. if (portfolio_number != 0) {
  1214. avgprofit = (double) profitcum / portfolio_number;
  1215. max_portfolios_tickers = Math.max(max_portfolios_tickers, portfolio_number);
  1216. }
  1217. rs.updateDouble("profit_person", (double) avgprofit);
  1218. rs.updateRow();
  1219. number_of_persons++;
  1220. }
  1221. rs.close();
  1222. stmt.close();
  1223. // _________________ محاسبه ی رتبه
  1224. stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  1225. sql = "SELECT * FROM mosabegheh order by profit_person desc";
  1226. rs = stmt.executeQuery(sql);
  1227. rs.last();
  1228. int ranks[] = new int[rs.getRow() + 1];
  1229. rs.beforeFirst();
  1230. while (rs.next()) {// ذخیره موقت در یک متغیر
  1231. int rank = rs.getRow();
  1232. int portfolionumber = rs.getInt("rownumber");
  1233. ranks[portfolionumber] = rank;
  1234. }
  1235.  
  1236. sql = "SELECT * FROM mosabegheh";
  1237. rs = stmt.executeQuery(sql);
  1238. rs.beforeFirst();
  1239. while (rs.next()) { // ذخیره بر روی پایگاه داده از روی متغیر
  1240. rs.updateInt("rank", ranks[rs.getInt("rownumber")]);
  1241. rs.updateRow();
  1242. }
  1243. rs.close();
  1244. stmt.close();
  1245. msg(number_of_persons + " عدد سبد محاسبه شد.");
  1246.  
  1247. DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###");
  1248. txt_number_of_tickers.setText(floatformatter.format(max_portfolios_tickers));
  1249.  
  1250. } catch (SQLException ex) {
  1251. Logger.getLogger(Frame_Match.class.getName()).log(Level.SEVERE, null, ex);
  1252. }
  1253. }
  1254.  
  1255. private void txt_number_of_tickersKeyReleased(java.awt.event.KeyEvent evt) {
  1256. numberoftickers = Integer.parseInt(txt_number_of_tickers.getText());
  1257. }
  1258.  
  1259. public static void main(String args[]) {
  1260. /* Set the Nimbus look and feel */
  1261. //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
  1262. /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
  1263. * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
  1264. */
  1265. try {
  1266. for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
  1267. if ("Nimbus".equals(info.getName())) {
  1268. javax.swing.UIManager.setLookAndFeel(info.getClassName());
  1269. break;
  1270.  
  1271. }
  1272. }
  1273. } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
  1274. java.util.logging.Logger.getLogger(Frame_Match.class
  1275. .getName()).log(java.util.logging.Level.SEVERE, null, ex);
  1276.  
  1277. }
  1278. //</editor-fold>
  1279.  
  1280. //</editor-fold>
  1281.  
  1282. /* Create and display the form */
  1283. java.awt.EventQueue.invokeLater(new Runnable() {
  1284. @Override
  1285. public void run() {
  1286. new Frame_Match().setVisible(true);
  1287. }
  1288. });
  1289. }
  1290.  
  1291. // Variables declaration - do not modify
  1292. private javax.swing.JButton btn_get_buy_prices;
  1293. private javax.swing.JButton btn_get_portfolios;
  1294. private javax.swing.JButton btn_get_sell_prices;
  1295. private javax.swing.JButton btncomputation;
  1296. private javax.swing.JButton btnoutput;
  1297. private javax.swing.ButtonGroup buttonGroup1;
  1298. private javax.swing.JCheckBox chk_dont_merge;
  1299. private javax.swing.JLabel jLabel1;
  1300. private javax.swing.JTextField txt_number_of_tickers;
  1301. // End of variables declaration
  1302. private javax.swing.JFileChooser jFileChooser1;
  1303.  
  1304. private String StandardizeTicker(String ticker) {
  1305. ticker = ticker.replaceAll(",", "");
  1306. ticker = ticker.replaceAll("\\s", "");
  1307. ticker = ticker.replaceAll("-", "_");
  1308. ticker = ticker.replaceAll("\\.", "_");
  1309. ticker = ticker.replaceAll("ك", "ک");
  1310. ticker = ticker.replaceAll("ي", "ی");
  1311. return ticker;
  1312. }
  1313. }
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!