package mhjexcel;

import java.awt.Desktop;
import java.awt.HeadlessException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import org.apache.poi.ss.util.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import static org.apache.poi.xssf.usermodel.examples.CreatePivotTable.setCellData;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
import org.apache.poi.xssf.usermodel.TextDirection;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.RichTextString;

import java.util.Map;
import java.util.HashMap;
import java.util.Calendar;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import static org.apache.poi.ss.usermodel.Font.COLOR_RED;

public class Frame_Match extends javax.swing.JFrame {

	public static File mhjfile;
	public static FileOutputStream mhjfileoutput;
	public static FileInputStream mhjfileinput;

	public static XSSFWorkbook mhjworkbook;
	public static XSSFSheet mhjsheet;
	public static XSSFRow mhjrow;
	public static XSSFCell mhjcell;

	public static String cellcontents;
	public static String mhjpath;
	public static String mhjextension;
	public static String mhjfilename;
	public static String mhjfulladdress;

	String mhjsheetname;
	int rowindex;//int rowindex = Integer.parseInt(txtrow.getText());
	int colindex;//int colindex = Integer.parseInt(txtcol.getText());
	int firstrow;
	int lastrow;
	int firstcol;
	int lastcol;
	int sheetindex;//int sheetindex = Integer.parseInt(txtsheet.getText());
	DataFormatter mhjdataformater = new DataFormatter();

	Connection con;
	Statement stmt;
	public static String sql;
	public static ResultSet rs;

	int rsrow;
	int firsrstrow;
	int lastrsrow;

	String ticker;
//	Double price;
	Double volume;
	Double price_balance;
	Double volume_balance;
	Integer kind;
	String comment;
	Integer date_year;
	Integer date_month;
	Integer date_day;
	Integer write_year;
	Integer write_month;
	Integer write_day;
	String treater2;
	String bill_or_link;

	int numberofallowedemptycells;

	public int outcounter;
	private String tablename;

	int colcounter = 0;
	int lastcolcounter = 6;
	int emptyrowcounter = 0;
	final int allowedemptyrows = 3;
	int emptycellcounter = 0;
	final int allowedemptycols = 13;
	boolean flagemtycols = false;
	int numberoftickers = 6;
	final int firstticker = 1;
	final double unknownvalue = 0.123456789;
	final int number_of_manual_prices = 100;

	String manual_tickers_names[] = new String[number_of_manual_prices];
	double manual_tickers_buys[] = new double[number_of_manual_prices];
	double manual_tickers_sells[] = new double[number_of_manual_prices];

	public Frame_Match() {
		initComponents();
	}

	public void err(Exception e) {

		String sometext = String.valueOf(e);
		outcounter++;
		System.out.println("************Exception**********" + outcounter);
		System.out.println("(line: "
				+ Thread.currentThread().getStackTrace()[0].getLineNumber()
				+ "+" + Thread.currentThread().getStackTrace()[1].getLineNumber()
				+ "+" + Thread.currentThread().getStackTrace()[2].getLineNumber()
				+ "+" + Thread.currentThread().getStackTrace()[3].getLineNumber()
				+ "+" + Thread.currentThread().getStackTrace()[4].getLineNumber()
				+ ")");
		System.out.println("\n" + sometext);
		System.out.println("\n" + String.valueOf(e.getMessage()));
		System.out.println("\n" + String.valueOf(e.getCause()));

		System.out.println("************End of Exception reporting*********");

		Logger.getLogger(Frame_Ticker.class.getName()).log(Level.SEVERE, null, e);
	}

	public void out(String sometext) {
		outcounter++;
		sometext = "\n" + outcounter + "-(line" + Thread.currentThread().getStackTrace()[2].getLineNumber() + ")==> " + sometext;
		System.out.println(sometext);
	}

	public void out(Object obj) {
		try {
			String text = String.valueOf(obj);
			out(text);
		} catch (Exception e) {
			System.out.println("مشکل در out");
		}
	}

	public static void msg(String text) {
		JOptionPane.showMessageDialog(null, text);
	}

	@SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    private void initComponents() {

        buttonGroup1 = new javax.swing.ButtonGroup();
        btn_get_portfolios = new javax.swing.JButton();
        btn_get_buy_prices = new javax.swing.JButton();
        btn_get_sell_prices = new javax.swing.JButton();
        btnoutput = new javax.swing.JButton();
        btncomputation = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();
        txt_number_of_tickers = new javax.swing.JTextField();
        chk_dont_merge = new javax.swing.JCheckBox();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);

        btn_get_portfolios.setText("دریافت سبد های پیشنهادی");
        btn_get_portfolios.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btn_get_portfoliosActionPerformed(evt);
            }
        });

        btn_get_buy_prices.setText("دریافت قیمت های اولیه");
        btn_get_buy_prices.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btn_get_buy_pricesActionPerformed(evt);
            }
        });

        btn_get_sell_prices.setText("دریافت قیمت های امروز");
        btn_get_sell_prices.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btn_get_sell_pricesActionPerformed(evt);
            }
        });

        btnoutput.setText("تولید خروجی");
        btnoutput.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnoutputActionPerformed(evt);
            }
        });

        btncomputation.setText("محاسبه سبد ها");
        btncomputation.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btncomputationActionPerformed(evt);
            }
        });

        jLabel1.setText("بیشینه نماد های درون سبد:");

        txt_number_of_tickers.setEditable(false);
        txt_number_of_tickers.setText("6");
        txt_number_of_tickers.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyReleased(java.awt.event.KeyEvent evt) {
                txt_number_of_tickersKeyReleased(evt);
            }
        });

        chk_dont_merge.setSelected(true);
        chk_dont_merge.setText("عدم ادغام سلول ها");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(chk_dont_merge)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                        .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                .addComponent(btn_get_sell_prices, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(btn_get_buy_prices, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(btn_get_portfolios, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(btncomputation, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(btnoutput, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                            .addGap(10, 10, 10))
                        .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                            .addComponent(txt_number_of_tickers, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jLabel1)
                            .addContainerGap()))))
        );

        layout.linkSize(javax.swing.SwingConstants.HORIZONTAL, new java.awt.Component[] {btn_get_buy_prices, btn_get_portfolios, btn_get_sell_prices, btncomputation});

        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(26, 26, 26)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(txt_number_of_tickers, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btn_get_portfolios)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btn_get_buy_prices)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btn_get_sell_prices)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btncomputation)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btnoutput)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(chk_dont_merge)
                .addContainerGap(84, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>                        

    private void btn_get_portfoliosActionPerformed(java.awt.event.ActionEvent evt) {                                                   
		try {
			mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\مسابقه.xlsx");
			jFileChooser1 = new javax.swing.JFileChooser();
			jFileChooser1.setCurrentDirectory(mhjfile);
			int result = jFileChooser1.showOpenDialog(this);
			if (result == JFileChooser.CANCEL_OPTION) {
				return;
			}
			mhjfile = jFileChooser1.getSelectedFile();

			mhjfileinput = new FileInputStream(mhjfile);
			mhjworkbook = new XSSFWorkbook(mhjfileinput);
			mhjsheet = mhjworkbook.cloneSheet(0);
			XSSFDataFormat format = mhjworkbook.createDataFormat();
			con = mhjexcel.con;
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "delete FROM mosabegheh";
			stmt.executeUpdate(sql);
			sql = "SELECT * FROM mosabegheh";
			rs = stmt.executeQuery(sql);
			firstrow = 1;
//			firstcol = 1;//تغییر داده نشود
//			lastcol = 5;
			// ________________________ حقله روی شرکت کنندگان و سطر ها
			for (rowindex = firstrow;; rowindex++) { // 
				mhjrow = mhjsheet.getRow(rowindex);
				// ___________________________ بررسی انتهای سطر ها
				if (mhjrow == null) {
					System.out.println(
							//"پایان سطر های ورکشیت و پایان کار جمع آوری داده ها" +
							"\n تعداد سطر های خوانده شده:" + rowindex
					);
					break;
				}
				rs.moveToInsertRow();
				mhjcell = mhjrow.getCell(0);
				cellcontents = mhjdataformater.formatCellValue(mhjcell);
				// _____________________بررسی نمادهای نامعلوم و قیمت های دستی 
				if (cellcontents.equals("نمادهای دستی") || cellcontents.equals("")) {
					int manual_tickers_counter = 0;
					for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
						mhjcell = mhjrow.getCell(tickernumber);
						cellcontents = mhjdataformater.formatCellValue(mhjcell);
						String string_array[] = new String[2];
						if (cellcontents != null) {
							string_array = cellcontents.split("(?<=\\D)(?=\\d)|(?<=\\d)(?=\\D)");
						}
						if (string_array.length != 2) {
							break;
						}
						String ticker = string_array[0];
						double price = Double.parseDouble(string_array[1]);

						ticker = StandardizeTicker(ticker);
						if (ticker.contains("خرید")) {
							ticker = ticker.replaceAll("خرید", "");
//							rs.updateDouble("buyprice" + String.valueOf(tickernumber), price);
							manual_tickers_buys[manual_tickers_counter] = price;
						} else if (ticker.contains("فروش")) {
							ticker = ticker.replaceAll("فروش", "");
//							rs.updateDouble("buyprice" + String.valueOf(tickernumber), price);
							manual_tickers_sells[manual_tickers_counter] = price;
						} else {
							out("مشکل");
						}
						manual_tickers_names[manual_tickers_counter] = ticker;
						manual_tickers_counter++;
//						rs.updateString("portfolioname" + String.valueOf(tickernumber), ticker);
//						rs.insertRow();
					}
					continue;
				}
				// _______________ دریافت نام شرکت کنندگان _________________
				rs.updateString("person", cellcontents);
				// _______________ دریافت نماد های منتخب شرکت کنندگان  _________________
				int emptycells = 0;
				for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
//				for (colindex = firstticker; colindex <= numberoftickers; colindex++) {
					mhjcell = mhjrow.getCell(tickernumber);
					cellcontents = mhjdataformater.formatCellValue(mhjcell);//cellcontents=mhjcell.getStringCellValue();  	//mhjcell.setCellValue(cellcontents);
					cellcontents = cellcontents.replaceAll(",", "");
					cellcontents = cellcontents.replaceAll("\\s", "");
					cellcontents = cellcontents.replaceAll("-", "");
					cellcontents = cellcontents.replaceAll("\\.", "");
					cellcontents = cellcontents.replaceAll("ك", "ک");
					cellcontents = cellcontents.replaceAll("ي", "ی");

					cellcontents = cellcontents.replaceAll("آ", "ا");

					cellcontents = cellcontents.replaceAll("\\d", "");

					cellcontents = cellcontents.replaceAll("\\(", "");
					cellcontents = cellcontents.replaceAll("\\)", "");

					cellcontents = cellcontents.replaceAll("=", "");
					cellcontents = cellcontents.replaceAll("\\*", "");

					if (!cellcontents.equals("")) {
						rs.updateString("portfolioname" + String.valueOf(tickernumber - emptycells), cellcontents);
					} else {
						emptycells++;
					}
				}
				rs.insertRow();
			}
			rs.close();
			stmt.close();
			msg("سبد های شرکت کنندگان در مسابقه دریافت شد.");
		} catch (HeadlessException | IOException | SQLException | NumberFormatException e) {
			err(e);
		}
    }                                                  

    private void btn_get_buy_pricesActionPerformed(java.awt.event.ActionEvent evt) {                                                   
		try {
			jFileChooser1 = new javax.swing.JFileChooser();
			jFileChooser1.setCurrentDirectory(new File("C:\\Users\\Mohammad\\Desktop"));
			int result = jFileChooser1.showOpenDialog(this);
			if (result == JFileChooser.CANCEL_OPTION) {
				return;
			}
			mhjfile = jFileChooser1.getSelectedFile();
			mhjworkbook = new XSSFWorkbook(mhjfile);
			mhjsheet = mhjworkbook.getSheetAt(0);
			XSSFDataFormat format = mhjworkbook.createDataFormat();
			con = mhjexcel.con;
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "SELECT * FROM mosabegheh";
			rs = stmt.executeQuery(sql);
			firstrow = 3;
			firstcol = 1;//تغییر داده نشود
			lastcol = 5;
			int tickerscol = 0;//در اکسل
			int pricecol = 10;//در اکسل

//			int firstticker = 1;
//			int finalticker = 4;
			rs.beforeFirst();
			while (rs.next()) {
				for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
					String ticker = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (ticker == null || ticker.equals("")) {
						continue;
					}
					for (rowindex = firstrow;; rowindex++) {
						mhjrow = mhjsheet.getRow(rowindex);
						if (mhjrow == null) {
							boolean found = false;
							for (int i = 0; i < manual_tickers_names.length; i++) {
								String name = manual_tickers_names[i];
								double buy = manual_tickers_buys[i];
								if (name != null && name.equals(ticker)) {
									rs.updateDouble("buyprice" + String.valueOf(tickernumber), buy);
									rs.updateRow();
									found = true;
									break;
								}
							}
							if (!found) {
								out("قیمت خرید "
										+ ticker
										+ " پیدا نشد"
								);
							}
							break;
						}
						mhjcell = mhjrow.getCell(tickerscol);
						cellcontents = mhjdataformater.formatCellValue(mhjcell);
						cellcontents = cellcontents.replaceAll(",", "");
						cellcontents = cellcontents.replaceAll("\\s", "");
						cellcontents = cellcontents.replaceAll("-", "_");
						cellcontents = cellcontents.replaceAll("\\.", "_");
						cellcontents = cellcontents.replaceAll("ك", "ک");
						cellcontents = cellcontents.replaceAll("ي", "ی");

						cellcontents = cellcontents.replaceAll("آ", "ا");

						if (cellcontents.equals(ticker)) {
//							out(ticker + " پیدا شد در سطر شماره ی  " + rowindex);
							mhjcell = mhjrow.getCell(pricecol);
							cellcontents = mhjdataformater.formatCellValue(mhjcell);
							Double price = Double.parseDouble(cellcontents);
//							out("price = " + price);
							rs.updateDouble("buyprice" + String.valueOf(tickernumber), price);
							rs.updateRow();
							break;
						}
					}
				}
			}
			rs.close();
			stmt.close();
			msg("قیمت های اولیه دریافت شد");
		} catch (HeadlessException | IOException | InvalidFormatException | SQLException | NumberFormatException e) {
			err(e);
		}
    }                                                  

    private void btn_get_sell_pricesActionPerformed(java.awt.event.ActionEvent evt) {                                                    
		try {
			jFileChooser1 = new javax.swing.JFileChooser();
			jFileChooser1.setCurrentDirectory(new File("C:\\Users\\Mohammad\\Desktop"));
			int result = jFileChooser1.showOpenDialog(this);
			if (result == JFileChooser.CANCEL_OPTION) {
				return;
			}
			mhjfile = jFileChooser1.getSelectedFile();
			mhjworkbook = new XSSFWorkbook(mhjfile);
			mhjsheet = mhjworkbook.getSheetAt(0);
			XSSFDataFormat format = mhjworkbook.createDataFormat();
			con = mhjexcel.con;
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "SELECT * FROM mosabegheh";
			rs = stmt.executeQuery(sql);
			firstrow = 3;
			firstcol = 1;//تغییر داده نشود
			lastcol = 5;
			int tickerscol = 0;//در اکسل
			int pricecol = 10;//در اکسل

//			int firstticker = 1;
//			int finalticker = 4;//numberoftickers
			rs.beforeFirst();
			while (rs.next()) {
				for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
					String ticker = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (ticker == null || ticker.equals("")) {
						continue;
					}
					for (rowindex = firstrow;; rowindex++) {
						mhjrow = mhjsheet.getRow(rowindex);

						if (mhjrow == null) {
							boolean found = false;
							for (int i = 0; i < manual_tickers_names.length; i++) {
								String name = manual_tickers_names[i];
								double sell = manual_tickers_sells[i];
								if (name != null && name.equals(ticker)) {
									rs.updateDouble("sellprice" + String.valueOf(tickernumber), sell);
									rs.updateRow();
									found = true;
									break;
								}
							}
							if (!found) {
								out("قیمت فروش "
										+ ticker
										+ " پیدا نشد"
								);
							}
							break;
						}

						mhjcell = mhjrow.getCell(tickerscol);
						cellcontents = mhjdataformater.formatCellValue(mhjcell);
						cellcontents = cellcontents.replaceAll(",", "");
						cellcontents = cellcontents.replaceAll("\\s", "");
						cellcontents = cellcontents.replaceAll("_", "");
						cellcontents = cellcontents.replaceAll("-", "_");
						cellcontents = cellcontents.replaceAll("\\.", "_");
						cellcontents = cellcontents.replaceAll("ك", "ک");
						cellcontents = cellcontents.replaceAll("ي", "ی");

						cellcontents = cellcontents.replaceAll("آ", "ا");

						if (cellcontents.equals(ticker)) {
//							out(ticker + " پیدا شد در سطر شماره ی  " + rowindex);
							mhjcell = mhjrow.getCell(pricecol);
							cellcontents = mhjdataformater.formatCellValue(mhjcell);
							Double price = Double.parseDouble(cellcontents);
//							out("price = " + price);
							rs.updateDouble("sellprice" + String.valueOf(tickernumber), price);
							rs.updateRow();
							break;
						}
					}
				}
			}
			rs.close();
			stmt.close();
			msg("قیمت های امروز دریافت شد.");
		} catch (HeadlessException | IOException | InvalidFormatException | SQLException | NumberFormatException e) {
			err(e);
		}
    }                                                   

	private void setTableRowNumbers(String tablename) {
		try {
			con = mhjexcel.con;
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "SELECT * FROM " + tablename; //lets give it from search
			rs = stmt.executeQuery(sql);
			rs.beforeFirst();
			while (rs.next()) {
				rs.updateInt("rownumber", rs.getRow());
				rs.updateRow();
			}
			out("در جدول " + tablename + "به میزان " + rs.getRow() + "عدد سطر شماره گذاری شد");
			rs.close();
			stmt.close();

		} catch (Exception e) {
			err(e);
		}
	}

	private void write2ExcelOld() {
		try {
			int number_of_tickers = Integer.parseInt(txt_number_of_tickers.getText());
//			con = mhjexcel.con;
//			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//			sql = "SELECT * FROM mosabegheh order by profit_person desc";
//			rs = stmt.executeQuery(sql);
//			//___________________________________________
//			mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\نتایج.xlsx");
//			mhjfileoutput = new FileOutputStream(mhjfile);
//			mhjworkbook = new XSSFWorkbook();
			mhjsheet = mhjworkbook.createSheet("مدل 1");//mhjsheet = mhjworkbook.getSheetAt(sheetindex);//workbook exist from before
			//______________________styles_____________________

			DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###.##");

			CellStyle styleHeaders = mhjworkbook.createCellStyle();
			styleHeaders.setAlignment(HorizontalAlignment.CENTER);
			styleHeaders.setBorderBottom(BorderStyle.THIN);
			styleHeaders.setBorderLeft(BorderStyle.THIN);
			styleHeaders.setBorderRight(BorderStyle.THIN);
			styleHeaders.setBorderTop(BorderStyle.THIN);
			styleHeaders.setBottomBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setRightBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setLeftBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setTopBorderColor(IndexedColors.WHITE.getIndex());
			Font f = mhjworkbook.createFont();
			f.setFontHeightInPoints((short) 12);//set font 1 to 12 point type
			f.setColor((short) 0xc);//COLOR_RED
			f.setBold(true);
			styleHeaders.setFont(f);
			f.setColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setFont(f);
			styleHeaders.setFillForegroundColor(IndexedColors.BLUE.getIndex());
			styleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);

			CellStyle styleNormal = mhjworkbook.createCellStyle();
			styleNormal.setAlignment(HorizontalAlignment.CENTER);
			styleNormal.setBorderBottom(BorderStyle.THIN);
			styleNormal.setBorderLeft(BorderStyle.THIN);
			styleNormal.setBorderRight(BorderStyle.THIN);
			styleNormal.setBorderTop(BorderStyle.THIN);

			CellStyle styleTickers = mhjworkbook.createCellStyle();
			styleTickers.setAlignment(HorizontalAlignment.CENTER);
			styleTickers.setBorderBottom(BorderStyle.THIN);
			styleTickers.setBorderLeft(BorderStyle.THIN);
			styleTickers.setBorderRight(BorderStyle.THIN);
			styleTickers.setBorderTop(BorderStyle.THIN);
			styleTickers.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			styleTickers.setFillPattern(FillPatternType.SOLID_FOREGROUND);

			//___________________________________________
			for (int i = 0; i <= number_of_tickers + 1; i++) {
				mhjsheet.setColumnWidth((short) i, 4000);
			}
			//___________________________________________
//			mhjworkbook.setSheetName(0, "نتایج");
			mhjrow = mhjsheet.createRow(0);
//			mhjcell = mhjrow.createCell(0);
//			mhjcell.setCellValue("نتایج مسابقه");
//			mhjcell.setCellStyle(style1);
			mhjcell = mhjrow.createCell(0);
			mhjcell.setCellValue("شرکت کننده");
			mhjcell.setCellStyle(styleHeaders);
			for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
				mhjcell = mhjrow.createCell(tickernumber);
				mhjcell.setCellValue("نماد " + String.valueOf(tickernumber));
				mhjcell.setCellStyle(styleHeaders);
			}
			//___________________________________________
			rs.beforeFirst();
			while (rs.next()) {
				rowindex = rs.getRow() * 2 - 1;
				mhjrow = mhjsheet.createRow(rowindex);
				Row headerRow = mhjsheet.getRow(0);
				headerRow.setHeight((short) 0x249);//mhjrow.setHeightInPoints(25f);
//				mhjcell = mhjrow.createCell(0);
//				mhjcell.setCellValue("سبد");
				mhjcell = mhjrow.createCell(0);
				mhjcell.setCellValue(rs.getString("person"));
				mhjcell.setCellStyle(styleHeaders);
				for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
					mhjcell = mhjrow.createCell(tickernumber);
					mhjcell.setCellValue(rs.getString("portfolioname" + String.valueOf(tickernumber)));
					mhjcell.setCellStyle(styleTickers);
				}
				//_____________
				rowindex++;
				mhjrow = mhjsheet.createRow(rowindex);
//				mhjcell = mhjrow.createCell(0);
//				mhjcell.setCellValue("درصد");
				mhjcell = mhjrow.createCell(0);
				mhjcell.setCellValue(Double.parseDouble(floatformatter.format(rs.getDouble("profit_person"))));
				mhjcell.setCellStyle(styleNormal);
				for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
					mhjcell = mhjrow.createCell(tickernumber);
					mhjcell.setCellStyle(styleNormal);
					Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
					if (rs.wasNull()) {
						continue;
					}
					mhjcell.setCellValue(Double.parseDouble(floatformatter.format(thisprofit)));
				}
			}
//			XSSFPivotTable pivotTable = mhjsheet.createPivotTable(new AreaReference(new CellReference("a1"), new CellReference("c7")), new CellReference("a1"));
//			pivotTable.addRowLabel(0);
//			XSSFTable my_table = mhjsheet.createTable();
//			CTTable cttable = my_table.getCTTable();
//			AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(rowindex, numberoftickers + 1));
//			cttable.setRef(my_data_range.formatAsString());
//			cttable.setTotalsRowCount(rowindex+1);
//			cttable.setDisplayName("نتایج مسابقه");
//			cttable.setName("match_results");
//			cttable.setId(1L);
//			cttable.addNewTableColumns();
//			CTTableColumns columns = cttable.addNewTableColumns();
//			columns.setCount(5L); //define number of columns
//			for (int i = 0; i < 5; i++) {
//				CTTableColumn column = columns.addNewTableColumn();
//				column.setName("Column" + i);
//				column.setId(i + 1);
//			}
			//_________________________________________________
//			mhjworkbook.write(mhjfileoutput);
//			mhjworkbook.close();
//			mhjfileoutput.close();
//			stmt.close();
//			rs.close();
//			if (mhjfile.exists() || mhjfile.isDirectory()) {
//				Desktop.getDesktop().open(mhjfile);
////				Runtime.getRuntime().exec("explorer.exe /select , " + mhjfile);
//			}
			// _____________________  تعیین پهنای ستون ها _____________________________
//			for (int i = 0; i < HeaderNames.length; i++) {
//				mhjsheet.setColumnWidth((short) i, ColWidth[i]);
//			}
			for (int columnNumber = 0; columnNumber <= number_of_tickers; columnNumber++) {
				mhjsheet.autoSizeColumn(columnNumber, true);
			}
		} catch (SQLException | NumberFormatException e) {
			err(e);
		}
	}

	private void write2ExcelResults() {
		try {
			int number_of_tickers = Integer.parseInt(txt_number_of_tickers.getText());
//			con = mhjexcel.con;
//			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//			sql = "SELECT * FROM mosabegheh order by profit_person desc";
//			rs = stmt.executeQuery(sql);
//			//___________________________________________
//			mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\نتایج.xlsx");
//			mhjfileoutput = new FileOutputStream(mhjfile);
//			mhjworkbook = new XSSFWorkbook();
			mhjsheet = mhjworkbook.createSheet("نتایج");//mhjsheet = mhjworkbook.getSheetAt(sheetindex);//workbook exist from before
			//______________________styles_____________________
//			mhjsheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).setRightToLeft(true);

			DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###.##");

			CellStyle styleHeaders = mhjworkbook.createCellStyle();
			styleHeaders.setAlignment(HorizontalAlignment.CENTER);
			styleHeaders.setVerticalAlignment(styleHeaders.VERTICAL_CENTER);
			styleHeaders.setBorderBottom(BorderStyle.THIN);
			styleHeaders.setBorderLeft(BorderStyle.THIN);
			styleHeaders.setBorderRight(BorderStyle.THIN);
			styleHeaders.setBorderTop(BorderStyle.THIN);
			styleHeaders.setBottomBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setRightBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setLeftBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setTopBorderColor(IndexedColors.WHITE.getIndex());
			Font f = mhjworkbook.createFont();
			f.setFontHeightInPoints((short) 12);//set font 1 to 12 point type
			f.setColor((short) 0xc);//COLOR_RED
			f.setBold(true);
			styleHeaders.setFont(f);
			f.setColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setFont(f);
			styleHeaders.setFillForegroundColor(IndexedColors.BLUE.getIndex());
			styleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);

			CellStyle styleHeaders2 = styleHeaders;
			styleHeaders2.setAlignment(HorizontalAlignment.RIGHT);

			CellStyle styleNormal = mhjworkbook.createCellStyle();
			styleNormal.setAlignment(HorizontalAlignment.CENTER);
			styleNormal.setBorderBottom(BorderStyle.THIN);
			styleNormal.setBorderLeft(BorderStyle.THIN);
			styleNormal.setBorderRight(BorderStyle.THIN);
			styleNormal.setBorderTop(BorderStyle.THIN);

			CellStyle styleTickers = mhjworkbook.createCellStyle();
			styleTickers.setAlignment(HorizontalAlignment.CENTER);
			styleHeaders.setVerticalAlignment(styleHeaders.VERTICAL_CENTER);
			styleTickers.setBorderBottom(BorderStyle.THIN);
			styleTickers.setBorderLeft(BorderStyle.THIN);
			styleTickers.setBorderRight(BorderStyle.THIN);
			styleTickers.setBorderTop(BorderStyle.THIN);
			styleTickers.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			styleTickers.setFillPattern(FillPatternType.SOLID_FOREGROUND);

			CellStyle styleToogle = mhjworkbook.createCellStyle();

			for (int i = 0; i <= number_of_tickers + 1; i++) {
				mhjsheet.setColumnWidth((short) i, 6000);
			}
			//____________________هدر های جدول اکسل_______________________
//			mhjworkbook.setSheetName(0, "نتایج");
			mhjrow = mhjsheet.createRow(0);
			mhjcell = mhjrow.createCell(0);
			mhjcell.setCellValue("شرکت کننده");
			mhjcell.setCellStyle(styleHeaders);
			for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
				mhjcell = mhjrow.createCell(tickernumber);
				mhjcell.setCellValue("نماد " + String.valueOf(tickernumber));
				mhjcell.setCellStyle(styleHeaders);
			}
			//___________________________________________
			boolean oddcounter = true;
			rs.beforeFirst();
			while (rs.next()) {
				if (oddcounter) {
					styleToogle = styleNormal;
				} else {
					styleToogle = styleTickers;
				}
				oddcounter = !oddcounter;
				rowindex = rs.getRow();
				mhjrow = mhjsheet.createRow(rowindex);
				Row headerRow = mhjsheet.getRow(0);
				headerRow.setHeight((short) 0x249);
				mhjcell = mhjrow.createCell(0);
				mhjcell.setCellStyle(styleHeaders2);
				String Person = rs.getString("person");
				Double Person_profit = rs.getDouble("profit_person");
//				mhjcell.setCellValue(Person + " - " + floatformatter.format(Person_profit));
				mhjcell.setCellValue("\u200E" + Person + "  (" + floatformatter.format(Person_profit) + ")");
				for (int tickernumber = 1; tickernumber <= number_of_tickers; tickernumber++) {
					mhjcell = mhjrow.createCell(tickernumber);
					mhjcell.setCellStyle(styleToogle);
					Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
					String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (rs.wasNull()) {
						continue;
					}
//					mhjcell.setCellValue(tickername + " - " + floatformatter.format(thisprofit));
					mhjcell.setCellValue("\u200E" + tickername + "  (" + floatformatter.format(thisprofit) + ")");
					if (thisprofit == unknownvalue) {
						mhjcell.setCellValue("\u200E" + tickername + " (؟)");
					}
				}
			}
//			//_________________________________________________
//			mhjworkbook.write(mhjfileoutput);
//			mhjworkbook.close();
//			mhjfileoutput.close();
//			stmt.close();
//			rs.close();
//			if (mhjfile.exists() || mhjfile.isDirectory()) {
//				Desktop.getDesktop().open(mhjfile);
//			}
			// _____________________  تعیین پهنای ستون ها _____________________________
//			for (int i = 0; i < HeaderNames.length; i++) {
//				mhjsheet.setColumnWidth((short) i, ColWidth[i]);
//			}
			for (int columnNumber = 0; columnNumber <= number_of_tickers; columnNumber++) {
				mhjsheet.autoSizeColumn(columnNumber, true);
			}
		} catch (Exception e) {
			err(e);
		}
	}

	private void write2ExcelPortfolios() {
		try {
			sql = "select * from mosabegheh order by rownumber"; // می خواهیم ترتیب بر اساس وارد شدن سبد ها باشد 
			rs = stmt.executeQuery(sql);
			// _______________________ پارامتر ها _______________
			mhjsheet = mhjworkbook.createSheet("سبد ها");//mhjsheet = mhjworkbook.getSheetAt(sheetindex);//workbook exist from before

			rs.last();
			int participantsCount = rs.getRow();

			int totalheaderrows = 1;
			int rowheader = 0;
			int rowdetails = 1;

			int totalsubrows = 3;
			int subrowticker = 0;
			int subrowbuy = 1;
			int subrowsell = 2;

			int totalcols = 5;
			int colportfolionumber = 0;
			int colparticipant = 1;
			int colrank = 2;
			int colprofit = 3;
			int colkind = 4;

			int numOfTickers = Integer.parseInt(txt_number_of_tickers.getText());

			String HeaderNames[] = new String[totalcols + numOfTickers];
			HeaderNames[0] = "شماره سبد";
			HeaderNames[1] = "شرکت کننده";
			HeaderNames[2] = "رتبه";
			HeaderNames[3] = "سود";
			HeaderNames[4] = "نوع";
			for (int tickernumber = firstticker; tickernumber <= numOfTickers; tickernumber++) {
				HeaderNames[4 + tickernumber] = "نماد " + tickernumber;
			}

//			int ColWidth[] = new int[totalcols + numberoftickers];
//			ColWidth[0] = 2500;
//			ColWidth[1] = 6000;
//			ColWidth[2] = 1000;
//			ColWidth[3] = 2000;
//			ColWidth[4] = 6000;
//			for (int tickernumber = firstticker; tickernumber <= numberoftickers; tickernumber++) {
//				ColWidth[4 + tickernumber] = 6000;
//			}
			// _____________________ تولید سطر ها و ستون ها _______________
			for (int i = 0; i <= (participantsCount * totalsubrows); i++) {  // participantsCount + 1 -1 // one for header row and one for begining of participatns from 1.
				mhjrow = mhjsheet.createRow(i);
				for (int j = 0; j < ((totalcols + numOfTickers)); j++) {
					mhjcell = mhjrow.createCell(j);
				}
			}
			//______________________styles_____________________
//			mhjsheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).setRightToLeft(true);

			DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###.##");

			CellStyle styleHeaders = mhjworkbook.createCellStyle();
			styleHeaders.setAlignment(HorizontalAlignment.CENTER);
			styleHeaders.setVerticalAlignment(styleHeaders.VERTICAL_CENTER);
			styleHeaders.setBorderBottom(BorderStyle.THIN);
			styleHeaders.setBorderLeft(BorderStyle.THIN);
			styleHeaders.setBorderRight(BorderStyle.THIN);
			styleHeaders.setBorderTop(BorderStyle.THIN);
			styleHeaders.setBottomBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setRightBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setLeftBorderColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setTopBorderColor(IndexedColors.WHITE.getIndex());
			Font f = mhjworkbook.createFont();
			f.setFontHeightInPoints((short) 12);//set font 1 to 12 point type
			f.setColor((short) 0xc);//COLOR_RED
			f.setBold(true);
			styleHeaders.setFont(f);
			f.setColor(IndexedColors.WHITE.getIndex());
			styleHeaders.setFont(f);
			styleHeaders.setFillForegroundColor(IndexedColors.BLUE.getIndex());
			styleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);

			CellStyle styleNormal = mhjworkbook.createCellStyle();
			styleNormal.setAlignment(HorizontalAlignment.CENTER);
			styleNormal.setVerticalAlignment(styleNormal.VERTICAL_CENTER);
			styleNormal.setBorderBottom(BorderStyle.THIN);
			styleNormal.setBorderLeft(BorderStyle.THIN);
			styleNormal.setBorderRight(BorderStyle.THIN);
			styleNormal.setBorderTop(BorderStyle.THIN);

			CellStyle styleTickers = mhjworkbook.createCellStyle();
			styleTickers.setAlignment(HorizontalAlignment.CENTER);
			styleTickers.setVerticalAlignment(styleTickers.VERTICAL_CENTER);
			styleTickers.setBorderBottom(BorderStyle.THIN);
			styleTickers.setBorderLeft(BorderStyle.THIN);
			styleTickers.setBorderRight(BorderStyle.THIN);
			styleTickers.setBorderTop(BorderStyle.THIN);
			styleTickers.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			styleTickers.setFillPattern(FillPatternType.SOLID_FOREGROUND);

			CellStyle styleToogle = mhjworkbook.createCellStyle();
			//________________________هدر های اکسل ___________________
			mhjrow = mhjsheet.getRow(rowheader);

			for (int i = 0; i < HeaderNames.length; i++) {
				mhjcell = mhjrow.getCell(i);
				mhjcell.setCellValue(HeaderNames[i]);
				mhjcell.setCellStyle(styleHeaders);
//				mhjsheet.setColumnWidth((short) i, ColWidth[i]);
			}
			Row headerRow = mhjsheet.getRow(rowheader);
			headerRow.setHeight((short) 0x249);

			//______________________سایر سطر ها_____________________
			boolean oddcounter = true;
			rs.beforeFirst();
			while (rs.next()) {
				if (oddcounter) {
					styleToogle = styleNormal;
				} else {
					styleToogle = styleTickers;
				}
				oddcounter = !oddcounter;
				//______________________  ستون شماره سبد _____________________
				rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails;
				mhjrow = mhjsheet.getRow(rowindex);
				mhjcell = mhjrow.getCell(colportfolionumber);
				mhjcell.setCellStyle(styleHeaders);
				mhjcell.setCellValue(rs.getInt("rownumber"));
				mhjrow = mhjsheet.getRow(rowindex + 1);
				mhjcell = mhjrow.getCell(colportfolionumber);
				mhjcell.setCellStyle(styleHeaders);
//				mhjcell.setCellValue("_");
				mhjrow = mhjsheet.getRow(rowindex + 2);
				mhjcell = mhjrow.getCell(colportfolionumber);
				mhjcell.setCellStyle(styleHeaders);
//				mhjcell.setCellValue("_");
				if (!chk_dont_merge.isSelected()) {
					mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colportfolionumber, colportfolionumber));
				}
				//______________________  ستون رتبه _____________________
				rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails;
				mhjrow = mhjsheet.getRow(rowindex);
				mhjcell = mhjrow.getCell(colrank);
				mhjcell.setCellStyle(styleHeaders);
				mhjcell.setCellValue(rs.getInt("rank"));
				mhjrow = mhjsheet.getRow(rowindex + 1);
				mhjcell = mhjrow.getCell(colrank);
				mhjcell.setCellStyle(styleHeaders);
//				mhjcell.setCellValue("_");
				mhjrow = mhjsheet.getRow(rowindex + 2);
				mhjcell = mhjrow.getCell(colrank);
				mhjcell.setCellStyle(styleHeaders);
//				mhjcell.setCellValue("_");
				if (!chk_dont_merge.isSelected()) {
					mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colrank, colrank));
				}
				//______________________  ستون  سود _____________________
				rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails; //یکی به خاطر اینکه پایگاه داده از یک شروع میشه و این از صفر یکی هم به خاطر اینکه از بین سه تا بیاد وسط
				mhjrow = mhjsheet.getRow(rowindex);
				mhjcell = mhjrow.getCell(colprofit);
				mhjcell.setCellStyle(styleHeaders);
				Double Person_profit = rs.getDouble("profit_person");
				mhjcell.setCellValue(Double.parseDouble(floatformatter.format(Person_profit)));
				mhjrow = mhjsheet.getRow(rowindex + 1);
				mhjcell = mhjrow.getCell(colprofit);
				mhjcell.setCellStyle(styleHeaders);
//				mhjcell.setCellValue("_");
				mhjrow = mhjsheet.getRow(rowindex + 2);
				mhjcell = mhjrow.getCell(colprofit);
				mhjcell.setCellStyle(styleHeaders);
//				mhjcell.setCellValue("_");
				if (!chk_dont_merge.isSelected()) {
					mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colprofit, colprofit));
				}
				//______________________  ستون شرکت کنندگان _____________________
				rowindex = (rs.getRow() * totalsubrows) - totalsubrows + rowdetails; //یکی به خاطر اینکه پایگاه داده از یک شروع میشه و این از صفر یکی هم به خاطر اینکه از بین سه تا بیاد وسط
				mhjrow = mhjsheet.getRow(rowindex);
				mhjcell = mhjrow.getCell(colparticipant);
				mhjcell.setCellStyle(styleHeaders);
				String Person = rs.getString("person");
				mhjcell.setCellValue("\u200E" + Person);
				mhjrow = mhjsheet.getRow(rowindex + 1);
				mhjcell = mhjrow.getCell(colparticipant);
				mhjcell.setCellStyle(styleHeaders);
				mhjrow = mhjsheet.getRow(rowindex + 2);
				mhjcell = mhjrow.getCell(colparticipant);
				mhjcell.setCellStyle(styleHeaders);
				if (!chk_dont_merge.isSelected()) {
					mhjsheet.addMergedRegion(new CellRangeAddress(rowindex, rowindex + 2, colparticipant, colparticipant));
				}
				//_____________________  ستون نوع عمل _________________________
				mhjrow = mhjsheet.getRow(rowindex + subrowticker);
				mhjcell = mhjrow.getCell(colkind);
				mhjcell.setCellStyle(styleToogle);
				mhjcell.setCellValue("نماد");
				mhjrow = mhjsheet.getRow(rowindex + subrowbuy);
				mhjcell = mhjrow.getCell(colkind);
				mhjcell.setCellStyle(styleToogle);
				mhjcell.setCellValue("خرید");
				mhjrow = mhjsheet.getRow(rowindex + subrowsell);
				mhjcell = mhjrow.getCell(colkind);
				mhjcell.setCellStyle(styleToogle);
				mhjcell.setCellValue("فروش");
				//_____________________  ستون نوع عمل _________________________
				// _____________________ اول نماد _____________________________
				mhjrow = mhjsheet.getRow(rowindex + subrowticker);
				for (int tickernumber = 1; tickernumber <= numOfTickers; tickernumber++) {
					mhjcell = mhjrow.getCell(tickernumber + colkind);
					mhjcell.setCellStyle(styleToogle);
					Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
					String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (rs.wasNull()) {
						continue;
					}
					mhjcell.setCellValue("\u200E" + tickername + " (" + floatformatter.format(thisprofit) + ")");
					if (thisprofit == unknownvalue) {
						mhjcell.setCellValue("\u200E" + tickername + " (؟)");
					}
				}
				// _____________________ دوم خرید _____________________________
				mhjrow = mhjsheet.getRow(rowindex + subrowbuy);
				for (int tickernumber = 1; tickernumber <= numOfTickers; tickernumber++) {
					mhjcell = mhjrow.getCell(tickernumber + colkind);
					mhjcell.setCellStyle(styleToogle);
					Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
					String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (rs.wasNull()) {
						continue;
					}
					double buy = rs.getDouble("buyprice" + String.valueOf(tickernumber));
					mhjcell.setCellValue(buy);
					if (buy == 0) {
						mhjcell.setCellValue("نامعلوم");
					}
				}
				// _____________________ سوم فروش _____________________________
				mhjrow = mhjsheet.getRow(rowindex + subrowsell);
				for (int tickernumber = 1; tickernumber <= numOfTickers; tickernumber++) {
					mhjcell = mhjrow.getCell(tickernumber + colkind);
					mhjcell.setCellStyle(styleToogle);
					Double thisprofit = rs.getDouble("profit" + String.valueOf(tickernumber));
					String tickername = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (rs.wasNull()) {
						continue;
					}
					double sell = rs.getDouble("sellprice" + String.valueOf(tickernumber));
					mhjcell.setCellValue(sell);
					if (sell == 0) {
						mhjcell.setCellValue("نامعلوم");
					}
				}
			}
			// _____________________  تعیین پهنای ستون ها _____________________________
//			for (int i = 0; i < HeaderNames.length; i++) {
//				mhjsheet.setColumnWidth((short) i, ColWidth[i]);
//			}
			for (int columnNumber = 0; columnNumber <= totalcols + numOfTickers; columnNumber++) {
				mhjsheet.autoSizeColumn(columnNumber, true);
			}
		} catch (SQLException | NumberFormatException e) {
			err(e);
		}
	}

    private void btnoutputActionPerformed(java.awt.event.ActionEvent evt) {                                          
		try {
//___________________________________________
			con = mhjexcel.con;
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "SELECT * FROM mosabegheh order by profit_person desc";
			rs = stmt.executeQuery(sql);
//___________________________________________
			mhjfile = new File("C:\\Users\\Mohammad\\Desktop\\نتایج.xlsx");
			mhjfileoutput = new FileOutputStream(mhjfile);
			mhjworkbook = new XSSFWorkbook();
//		if (radio1.isSelected()) {
//			write2Excel1();
//		} else if (radio2.isSelected()) {
//			write2Excel2();
//		} else if (radio3.isSelected()) {
//			write2Excel3();
//		}
			write2ExcelResults();
			out("نتایج تولید شد.");
			write2ExcelPortfolios();
			out("سبد ها تولید شد.");
			write2ExcelOld();
			out("مدل اول تولید شد.");
//_________________________________________________
			mhjworkbook.write(mhjfileoutput);
			mhjworkbook.close();
			mhjfileoutput.close();
			stmt.close();
			rs.close();
			if (mhjfile.exists() || mhjfile.isDirectory()) {
				Desktop.getDesktop().open(mhjfile);
			}
		} catch (SQLException | IOException e) {
			err(e);
		}
    }                                         

    private void btncomputationActionPerformed(java.awt.event.ActionEvent evt) {                                               
		try {
			// ___________________ numbering rows of database (in table mosabegheh)
			setTableRowNumbers("mosabegheh");
			// ___________________ computation of profits
			con = mhjexcel.con;
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "SELECT * FROM mosabegheh";
			rs = stmt.executeQuery(sql);
			double number_of_persons = 0;

			double max_portfolios_tickers = 0;

			rs.beforeFirst();
			while (rs.next()) {
				double profitcum = 0;
				double portfolio_number = 0;
				for (int tickernumber = 1; tickernumber <= numberoftickers; tickernumber++) {
					String ticker = rs.getString("portfolioname" + String.valueOf(tickernumber));
					if (ticker != null) {
						double buy = rs.getDouble("buyprice" + String.valueOf(tickernumber));
						if (buy == 0) {
							out("مشکل در قیمت خرید " + rs.getString("person") + " profit" + String.valueOf(tickernumber));
							rs.updateDouble("profit" + String.valueOf(tickernumber), unknownvalue);
							continue;
						}
						double sell = rs.getDouble("sellprice" + String.valueOf(tickernumber));
						if (sell == 0) {
							out("مشکل در قیمت فروش " + rs.getString("person") + " profit" + String.valueOf(tickernumber));
							rs.updateDouble("profit" + String.valueOf(tickernumber), unknownvalue);
							continue;
						}
						portfolio_number++;
						double profit = sell - buy;
						double profitpercents = 100 * profit / buy;
						rs.updateDouble("profit" + String.valueOf(tickernumber), profitpercents);
						profitcum += profitpercents;
					}
				}
//				out(profitcum);
//				out(portfolio_number);
				double avgprofit = 0;
				if (portfolio_number != 0) {
					avgprofit = (double) profitcum / portfolio_number;
					max_portfolios_tickers = Math.max(max_portfolios_tickers, portfolio_number);
				}
				rs.updateDouble("profit_person", (double) avgprofit);
				rs.updateRow();
				number_of_persons++;
			}
			rs.close();
			stmt.close();
			// _________________ محاسبه ی رتبه 			
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			sql = "SELECT * FROM mosabegheh order by profit_person desc";
			rs = stmt.executeQuery(sql);
			rs.last();
			int ranks[] = new int[rs.getRow() + 1];
			rs.beforeFirst();
			while (rs.next()) {// ذخیره موقت در یک متغیر
				int rank = rs.getRow();
				int portfolionumber = rs.getInt("rownumber");
				ranks[portfolionumber] = rank;
			}

			sql = "SELECT * FROM mosabegheh";
			rs = stmt.executeQuery(sql);
			rs.beforeFirst();
			while (rs.next()) { // ذخیره بر روی پایگاه داده از روی متغیر
				rs.updateInt("rank", ranks[rs.getInt("rownumber")]);
				rs.updateRow();
			}
			rs.close();
			stmt.close();
			msg(number_of_persons + " عدد سبد محاسبه شد.");

			DecimalFormat floatformatter = new DecimalFormat("###,###,###,###,###");
			txt_number_of_tickers.setText(floatformatter.format(max_portfolios_tickers));

		} catch (SQLException ex) {
			Logger.getLogger(Frame_Match.class.getName()).log(Level.SEVERE, null, ex);
		}
    }                                              

    private void txt_number_of_tickersKeyReleased(java.awt.event.KeyEvent evt) {                                                  
		numberoftickers = Integer.parseInt(txt_number_of_tickers.getText());
    }                                                 

	public static void main(String args[]) {
		/* Set the Nimbus look and feel */
		//<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
		/* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
		 */
		try {
			for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
				if ("Nimbus".equals(info.getName())) {
					javax.swing.UIManager.setLookAndFeel(info.getClassName());
					break;

				}
			}
		} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
			java.util.logging.Logger.getLogger(Frame_Match.class
					.getName()).log(java.util.logging.Level.SEVERE, null, ex);

		}
		//</editor-fold>

		//</editor-fold>

		/* Create and display the form */
		java.awt.EventQueue.invokeLater(new Runnable() {
			@Override
			public void run() {
				new Frame_Match().setVisible(true);
			}
		});
	}

    // Variables declaration - do not modify                     
    private javax.swing.JButton btn_get_buy_prices;
    private javax.swing.JButton btn_get_portfolios;
    private javax.swing.JButton btn_get_sell_prices;
    private javax.swing.JButton btncomputation;
    private javax.swing.JButton btnoutput;
    private javax.swing.ButtonGroup buttonGroup1;
    private javax.swing.JCheckBox chk_dont_merge;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JTextField txt_number_of_tickers;
    // End of variables declaration                   
	private javax.swing.JFileChooser jFileChooser1;

	private String StandardizeTicker(String ticker) {
		ticker = ticker.replaceAll(",", "");
		ticker = ticker.replaceAll("\\s", "");
		ticker = ticker.replaceAll("-", "_");
		ticker = ticker.replaceAll("\\.", "_");
		ticker = ticker.replaceAll("ك", "ک");
		ticker = ticker.replaceAll("ي", "ی");
		return ticker;
	}
}