/**
* Converts an integer into the alphabet base (A-Z).
*
* @param int $n This is the number to convert.
* @return string The converted number.
* @author Theriault
*
*/
function num2alpha($n) {
$r = '';
for ($i = 1; $n >= 0 && $i < 10; $i++) {
$r = chr(0x41 + ($n % pow(26, $i) / pow(26, $i - 1))) . $r;
$n -= pow(26, $i);
}
return $r;
}
/**
* Converts an alphabetic string into an integer.
*
* @param int $n This is the number to convert.
* @return string The converted number.
* @author Theriault
*
*/
function alpha2num($a) {
$r = 0;
$l = strlen($a);
for ($i = 0; $i < $l; $i++) {
$r += pow(26, $i) * (ord($a[$l - $i - 1]) - 0x40);
}
return $r - 1;
}
/**
* 엑셀 다운로드
* 2022-07-06 잘못된 URL은 하이퍼링크 처리하지 않도록 수정
*/
function downloadExcelByRows($label,$rows){
$link_style_array = array(
'font' => array(
'color' => array('rgb' => '0000FF'),
'underline' => 'single',
)
);
require_once(APPPATH.'/libraries/PHPExcel.php');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($label)
->setLastModifiedBy($label)
->setTitle($label)
->setSubject($label)
->setDescription($label)
->setKeywords($label)
->setCategory($label);
$sheet = $objPHPExcel->setActiveSheetIndex(0);
$rowI = 0;
foreach($rows as $row){
$rowI++;
$rowAlpha = num2alpha($rowI);
$i = 0;
foreach($row as $v){
$calA = num2alpha($i);
$pos = $calA.$rowI;
// echo $pos;
// $sheet->setCellValue($pos, $v);
if((strpos($v,'http://')===0 ||strpos($v,'https://')===0) && filter_var($v,FILTER_VALIDATE_URL) !== false){ //자동 링크처리
// $a_text = new PHPExcel_RichText();
// $a_text->createTextRun($v)->getFont()->setUnderline(true);
$sheet->setCellValueExplicit($pos, $v, PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->getCell($pos)->getHyperlink()->setUrl($v);
$sheet->getStyle($pos)->applyFromArray($link_style_array);
}else{
$sheet->setCellValueExplicit($pos, $v, PHPExcel_Cell_DataType::TYPE_STRING);
}
$i++;
}
}
// exit;
// excel 2007 .xlsx
// Generate xlsx file in 2007excel format
header ('Content-Type: application / vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header ('Content-Disposition: attachment; filename = "'.$label.'.xlsx"');
header ('Cache-Control: max-age = 0');
$objWriter = PHPExcel_IOFactory::createWriter ($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
//--- 메모리 초기화
$objPHPExcel->disconnectWorksheets();
$objPHPExcel->garbageCollect();
unset($objPHPExcel);
exit();
return;
}
/**
* 다중시트 엑셀 다운로드
* 2022-07-06 잘못된 URL은 하이퍼링크 처리하지 않도록 수정
*/
function downloadExcelByKrs($label,$krs){
$link_style_array = array(
'font' => array(
'color' => array('rgb' => '0000FF'),
'underline' => 'single',
)
);
require_once(APPPATH.'/libraries/PHPExcel.php');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($label)
->setLastModifiedBy($label)
->setTitle($label)
->setSubject($label)
->setDescription($label)
->setKeywords($label)
->setCategory($label);
$sheeti = 0;
foreach($krs as $sheetname => $rows){
if($sheeti!=0) $objPHPExcel->createSheet($sheeti);
$sheet = $objPHPExcel->setActiveSheetIndex($sheeti);
$sheet->setTitle($sheetname);
$rowI = 0;
foreach($rows as $row){
$rowI++;
// $rowAlpha = num2alpha($rowI);
$i = 0;
foreach($row as $v){
$calA = num2alpha($i);
$pos = $calA.$rowI;
// echo $pos;
// $sheet->setCellValue($pos, $v);
if((strpos($v,'http://')===0 ||strpos($v,'https://')===0) && filter_var($v,FILTER_VALIDATE_URL) !== false){ //자동 링크처리
// $a_text = new PHPExcel_RichText();
// $a_text->createTextRun($v)->getFont()->setUnderline(true);
$sheet->setCellValueExplicit($pos, $v, PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->getCell($pos)->getHyperlink()->setUrl($v);
$sheet->getStyle($pos)->applyFromArray($link_style_array);
}else{
$sheet->setCellValueExplicit($pos, $v, PHPExcel_Cell_DataType::TYPE_STRING);
}
$i++;
}
}
$sheeti++;
}
// exit;
// excel 2007 .xlsx
// Generate xlsx file in 2007excel format
header ('Content-Type: application / vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header ('Content-Disposition: attachment; filename = "'.$label.'.xlsx"');
header ('Cache-Control: max-age = 0');
$objWriter = PHPExcel_IOFactory::createWriter ($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
//--- 메모리 초기화
$objPHPExcel->disconnectWorksheets();
$objPHPExcel->garbageCollect();
unset($objPHPExcel);
exit();
return;
}
function excelToArray($inputFileName){
require_once(APPPATH.'/libraries/PHPExcel.php');
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
return $sheetData;
}