function getNameFromNumber($num)
{
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
function getMosType()
{
global $gTEXT;
require '../lib/PHPExcel.php';
$CountryId = $_GET['CountryId'];
$FacilityId = $_GET['FacilityId'];
$MonthId = $_GET['MonthId'];
$YearId = $_GET['YearId'];
$ItemGroupId = $_GET['ItemGroupId'];
$mosTypeId = $_REQUEST['MosTypeId'];
$CountryName = $_GET['CountryName'];
$MonthName = $_GET['MonthName'];
$ItemGroupName = $_GET['ItemGroupName'];
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->SetCellValue('A2', $gTEXT['Facility Inventory Control']);
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(array('font' => array('size' => '16', 'bold' => true)), 'A2');
$objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
$objPHPExcel->getActiveSheet()->SetCellValue('A3', 'Country Name : ' . $CountryName . ' , ' . ('Product Group : ' . $ItemGroupName));
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont();
$objPHPExcel->getActiveSheet()->duplicateStyleArray(array('font' => array('size' => '12')), 'A3');
$objPHPExcel->getActiveSheet()->mergeCells('A3:F3');
$objPHPExcel->getActiveSheet()->SetCellValue('A4', 'Month : ' . $MonthName . ' , ' . ('Year : ' . $Year));
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getFont();
$objPHPExcel->getActiveSheet()->duplicateStyleArray(array('font' => array('size' => '12')), 'A4');
$objPHPExcel->getActiveSheet()->mergeCells('A4:F4');
$objPHPExcel->getActiveSheet()->SetCellValue('A6', $gTEXT['Product Name'])->SetCellValue('B6', $gTEXT['MOS']);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(array('font' => array('size' => '12', 'bold' => true)), A6);
$objPHPExcel->getActiveSheet()->getStyle('A6:A6')->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(55);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14);
$sQuery = "SELECT MosTypeId, MosTypeName, MinMos, MaxMos, ColorCode \n\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t t_mostype\n\t\t\t\t\t\t\t\t\t\tWHERE (MosTypeId = {$mosTypeId} OR {$mosTypeId} = 0)\n\t\t\t\t\t\t\t\t\t\tORDER BY MosTypeId;";
$rResult = mysql_query($sQuery);
$output = array();
$z = 1;
$y = 6;
while ($row = mysql_fetch_array($rResult)) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($z, $y, $row['MosTypeName']);
$sd = getNameFromNumber($totColumnVal);
$objPHPExcel->getActiveSheet()->getStyle($sd . $y . ':' . $sd . $y)->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->duplicateStyleArray(array('font' => array('size' => '12', 'bold' => true)), $sd . $y);
$tmpRow['sTitle'] = $row['MosTypeName'];
$tmpRow['sClass'] = 'center-aln';
$output1[] = $row;
$z++;
}
$sQuery = "SELECT p.MosTypeId, ItemName, MOS FROM (SELECT\n\t\t\t\t a.ItemNo\n\t\t\t\t , b.ItemName\n\t\t\t\t , a.MOS\n\t\t\t\t\t,(SELECT MosTypeId FROM t_mostype x WHERE (MosTypeId = {$mosTypeId} OR {$mosTypeId} = 0) AND a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId\n\t\t\t\t\tFROM t_cnm_stockstatus a, t_itemlist b, t_cnm_masterstockstatus c\n\t\t\t\t\tWHERE a.itemno = b.itemno AND a.MOS IS NOT NULL AND a.MonthId = " . $_REQUEST['MonthId'] . " AND a.Year = '" . $_REQUEST['YearId'] . "' AND a.CountryId = " . $_REQUEST['CountryId'] . " AND a.ItemGroupId = " . $_REQUEST['ItemGroupId'] . " AND a.CNMStockId = c.CNMStockId" . " AND c.StatusId = 5 " . ") p\n\t\t\t\t\tWHERE (p.MosTypeId = {$mosTypeId} OR {$mosTypeId} = 0) \n\t\t\t\t\tORDER BY ItemName";
$rResult = mysql_query($sQuery);
$aData = array();
$r = mysql_query($sQuery);
$j = 7;
$y = 7;
if ($r) {
while ($row = mysql_fetch_array($rResult)) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $j, $row['ItemName']);
$z = 1;
foreach ($output1 as $rowMosType) {
if ($rowMosType['MosTypeId'] == $row['MosTypeId']) {
//$tmpRow[] = '<i class="fa fa-check-circle fa-lg" style="color:' . $rowMosType['ColorCode'] . ';font-size:2.5em;"></i>';
// '<td><span class="fa fa-check-circle fa-lg" style="color:' . $rowMosType['ColorCode'] . ';font-size:2.5em;text-align:center;"></span></td>';
$temp = explode('#', $rowMosType['ColorCode']);
$styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000'))), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $temp[1])));
$sd = getNameFromNumber($totColumnVal);
$objPHPExcel->getActiveSheet()->getStyle($sd . $y . ':' . $sd . $y)->applyFromArray($styleThinBlackBorderOutline);
$z++;
} else {
$z++;
}
}
$y++;
$j++;
}
}
if (function_exists('date_default_timezone_set')) {
date_default_timezone_set('UTC');
} else {
putenv("TZ=UTC");
}
$exportTime = date("Y-m-d_His", time());
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$file = 'ExportExcel' . $exportTime . '.xlsx';
$objWriter->save(str_replace('.php', '.xlsx', 'media/' . $file));
header('Location:media/' . $file);
}
/**
* Takes a 2D array and saves it as a nicely formatted Excel spreadsheet.
* Metadata columns are preserved, multiple worksheets are used, when appropriate and headers are maintained.
*
* @param string $Test_name File name to be used.
* @param unknown_type $instrument_table A 2D array of the data to be presented in Excel format
* @param unknown_type $dataDir The output directory.
*/
function writeExcel($Test_name, $instrument_table, $dataDir)
{
// $metaCols = array("PSCID", "CandID", "Visit_label", "Examiner_name", "Data_entry_completion_status", "Date_taken"); //metadata columns
$junkCols = array("CommentID", "UserID", "Examiner", "Testdate", "Data_entry_completion_status");
//columns to be removed
// create empty Excel file to fill up
// Create a new PHPExcel Object
$ExcelApplication = new PHPExcel();
$ExcelWorkSheet = $ExcelApplication->getSheet(0);
//ensure non-empty result set
if (count($instrument_table) == 0) {
//empty result set
echo "Empty: {$Test_name} [Contains no data]\n";
return;
// move on to the next instrument //nothing to save
}
//remove any undesired columns that came in from the DB query.
for ($i = 0; $i < count($instrument_table); $i++) {
$instrument_table[$i] = array_diff_key($instrument_table[$i], array_flip($junkCols));
}
// add all header rows
$headers = array_keys($instrument_table[0]);
$ExcelWorkSheet->fromArray($headers, ' ', 'A1');
// Bold Cyan Column headers
$numCol = count($instrument_table[0]) - 1;
$header = 'a1:' . getNameFromNumber($numCol) . '1';
$ExcelWorkSheet->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00e0ffff');
$hor_cen = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
$style = array('font' => array('bold' => true), 'alignment' => array('horizontal' => $hor_cen));
$ExcelWorkSheet->getStyle($header)->applyFromArray($style);
// add data to worksheet
$ExcelWorkSheet->fromArray($instrument_table, ' ', 'A2');
// Redimension columns to max size of data
for ($col = 0; $col <= $numCol; $col++) {
$ExcelWorkSheet->getColumnDimension(getNameFromNumber($col))->setAutoSize(true);
}
// save file to disk
print "Creating " . $Test_name . ".xls\n";
$writer = PHPExcel_IOFactory::createWriter($ExcelApplication, 'Excel2007');
$writer->save("{$dataDir}/{$Test_name}.xls");
unset($ExcelApplication);
}
