void XLS2SQL::xls2SQL(string fileName)
{
char t_fileName[fileName.length()+1];
strncpy(t_fileName,fileName.c_str(),sizeof(t_fileName));
t_fileName[sizeof(t_fileName)-1] = '\0';
pxb = xls_open(t_fileName,"UTF-8");
if(NULL == pxb)
{
cout<<"xls file open failed!";
}
pxs = xls_getWorkSheet(pxb,0);
xls_parseWorkSheet(pxs);
int rowNum = pxs->rows.lastrow;
row0 = &pxs->rows.row[0];
string colName = "";
string cellContent = "";
DSLFile df;
int index = 0;
for(r = 1;r <=rowNum;r++)
{
++index;
SymIpa sysIpa;
row = &pxs->rows.row[r];
int colNum = pxs->rows.lastcol;
Data data;
for(c = 0;c < colNum;c++)
{
colName = df.Pchar2Str(row0->cells.cell[c].str);
cellContent = df.Pchar2Str(row->cells.cell[c].str);
if(0 == strcmp(colName.c_str(),"SYM"))
{
sysIpa.SetSym(cellContent);
}else if(0 == strcmp(colName.c_str(),"IPA"))
{
sysIpa.SetIpa(cellContent);
}else if(0 == strcmp(colName.c_str(),"Example"))
{
sysIpa.SetExample(cellContent);
}else if(0 == strcmp(colName.c_str(),"PhoneID"))
{
//cout<<stoi(cellContent)<<endl;
sysIpa.SetId(index);
}
}
data.InsertSymIpa(&sysIpa);
}
cout<<"所有行数据输出完毕"<<endl;
xls_close_WS(pxs);
xls_close_WB(pxb);
}
int main(int argc, char *argv[])
{
xlsWorkBook* pWB;
xlsWorkSheet* pWS;
st_row_data* row;
WORD t, tt;
//액셀 문서를 연다.
pWB = xls_open("test.xls", "UTF-8");
if (pWB != NULL)
{
//워크 시트 갯수만큼 루프를 돈다.
for (int i = 0; i<pWB->sheets.count; i++)
{
printf("Sheet[%i] (%s) pos=%i\n", i, pWB->sheets.sheet[i].name, pWB->sheets.sheet[i].filepos);
//워크 시트를 얻어온 뒤
//데이터를 파싱한다.
pWS = xls_getWorkSheet(pWB, i);
xls_parseWorkSheet(pWS);
//워크 시트의 내용을 출력한다.
printf("Count of rows: %i\n", pWS->rows.lastrow + 1);
printf("Max col: %i\n", pWS->rows.lastcol);
for (t = 0; t <= pWS->rows.lastrow; t++)
{
row = (st_row_data*)&pWS->rows.row[t];
//xls_showROW(row);
for (tt = 0; tt <= pWS->rows.lastcol; tt++)
{
st_cell_data cell = row->cells.cell[tt];
printf("cell=%s\n", cell.str);
//xls_showCell((st_cell_data *)&row->cells.cell[tt]);
}
}
}
//xls_showBookInfo(pWB);
}
return 0;
}
int main(int argc, char *argv[])
{
xlsWorkBook* pWB;
xlsWorkSheet* pWS;
unsigned int i;
xls_debug=1;
if(argc != 2) {
printf("Need file arg\n");
exit(0);
}
struct st_row_data* row;
WORD t,tt;
pWB=xls_open(argv[1],"UTF-8");
if (pWB!=NULL)
{
for (i=0;i<pWB->sheets.count;i++)
printf("Sheet N%i (%s) pos %i\n",i,pWB->sheets.sheet[i].name,pWB->sheets.sheet[i].filepos);
pWS=xls_getWorkSheet(pWB,0);
xls_parseWorkSheet(pWS);
for (t=0;t<=pWS->rows.lastrow;t++)
{
row=&pWS->rows.row[t];
xls_showROW(row);
for (tt=0;tt<=pWS->rows.lastcol;tt++)
{
xls_showCell(&row->cells.cell[tt]);
}
}
printf("Count of rows: %i\n",pWS->rows.lastrow);
printf("Max col: %i\n",pWS->rows.lastcol);
printf("Count of sheets: %i\n",pWB->sheets.count);
xls_showBookInfo(pWB);
} else {
printf("pWB == NULL\n");
}
return 0;
}
void XlsReader::parseXls( const string &filePath ){
m_SheetArray.clear();
xlsWorkBook *p_Wb;
xlsWorkSheet *p_Ws;
p_Wb = xls_open( filePath.c_str(), "UTF-8" );
if( p_Wb == NULL ){
DEBUG_I("parse failed [ " << filePath << "] ");
}
for( int indexOfSheet = 0; indexOfSheet < p_Wb->sheets.count; ++indexOfSheet ){
p_Ws = xls_getWorkSheet( p_Wb, indexOfSheet );
xls_parseWorkSheet( p_Ws );
m_SheetArray.push_back( XlsSheet(*p_Ws) );
xls_close_WS( p_Ws ); // 源码示例中没有进行delete，只有close函数。
}
xls_close_WB( p_Wb); // 源码示例中没有进行delete，只有close函数。
}
/*--------------------------------------------------------------------------*/
int sci_xls_open(char *fname, unsigned long fname_len)
{
#undef IN
#define max_char_xls_open 256
int i = 0, m1 = 0, n1 = 0, l1 = 0, l2 = 0, one = 1, fd = 0, f_swap = 0;
int ierr = 0, ns = 0, result = 0;
double res;
char **sst = NULL;
char **Sheetnames = NULL;
int *Abspos = NULL;
int nsheets = 0;
char *filename_IN = NULL;
char TMP[max_char_xls_open];
char sep[2];
char *TMPDIR = NULL;
#ifdef _MSC_VER
sep[0] = '\\';
#else
sep[0] = '/';
#endif
sep[1] = '\0';
CheckLhs(4, 4);
CheckRhs(1, 1);
if (VarType(1) != sci_strings)
{
Scierror(999, "%s: Invalid type of input argument: String expected.", fname);
return 0;
}
/* checking variable file */
GetRhsVar(1, STRING_DATATYPE, &m1, &n1, &l1);
filename_IN = expandPathVariable(cstk(l1));
if (filename_IN)
{
/* bug 5615 */
/* remove blank characters @ the end */
int len = (int)strlen(filename_IN);
int i = 0;
if (len >= 1)
{
for (i = len - 1; i >= 0; i--)
{
if (filename_IN[i] == ' ')
{
filename_IN[i] = '\0';
}
else
{
break;
}
}
}
if (!FileExist(filename_IN))
{
Scierror(999, _("The file %s does not exist.\n"), filename_IN);
return 0;
}
}
TMPDIR = getTMPDIR();
strcpy(TMP, TMPDIR);
if (TMPDIR)
{
FREE(TMPDIR);
TMPDIR = NULL;
}
strcat(TMP, sep);
strcat(TMP, xls_basename(filename_IN));
result = ripole(filename_IN, TMP, 0, 0);
if (result != OLE_OK)
{
if (result == OLEER_NO_INPUT_FILE)
{
Scierror(999, _("The file %s does not exist.\n"), filename_IN);
}
else if (result == OLEER_NOT_OLE_FILE ||
result == OLEER_INSANE_OLE_FILE ||
result == OLEER_LOADFAT_BAD_BOUNDARY || result == OLEER_MINIFAT_READ_FAIL || result == OLEER_PROPERTIES_READ_FAIL)
{
Scierror(999, _("%s: File %s is not an ole2 file.\n"), fname, filename_IN);
if (filename_IN)
{
FREE(filename_IN);
filename_IN = NULL;
}
}
else if (result == -1)
{
Scierror(999, _("%s: Cannot open file %s.\n"), fname, filename_IN);
if (filename_IN)
{
FREE(filename_IN);
filename_IN = NULL;
}
}
return 0;
}
strcat(TMP, sep);
strcat(TMP, "Workbook");
C2F(mopen) (&fd, TMP, "rb", &f_swap, &res, &ierr);
if (ierr != 0)
{
Scierror(999, _("%s: There is no xls stream in the ole2 file %s.\n"), fname, filename_IN);
if (filename_IN)
{
FREE(filename_IN);
filename_IN = NULL;
}
return 0;
}
if (filename_IN)
{
FREE(filename_IN);
filename_IN = NULL;
}
CreateVar(Rhs + 1, MATRIX_OF_INTEGER_DATATYPE, &one, &one, &l2);
*istk(l2) = fd; /* logical unit */
xls_open(&ierr, &fd, &sst, &ns, &Sheetnames, &Abspos, &nsheets);
/*return *err:
* 0 = OK
* 1 = not an OLE file
* 2 = no Workbook included
* 3 = memory allocation problem
* 4 = incorrect file
* 5 = not a BIFF8 xls file
*/
switch (ierr)
{
case 0:
/* OK */
break;
case 1:
Scierror(999, _("%s: Not an ole2 file.\n"), fname);
return 0;
case 2:
Scierror(999, _("%s: The file has no Workbook directory.\n"), fname);
return 0;
case 3:
Scierror(999, _("%s: No more memory.\n"), fname);
return 0;
case 4:
Scierror(990, _("%s: Incorrect or corrupted file.\n"), fname);
return 0;
case 5:
Scierror(999, _("%s: Only BIFF8 file format is handled.\n"), fname);
return 0;
default:
break;
}
if (ns != 0)
{
/* Create a typed list to return the properties */
CreateVarFromPtr(Rhs + 2, MATRIX_OF_STRING_DATATYPE, &one, &ns, sst);
freeArrayOfString(sst, ns);
}
else
{
CreateVar(Rhs + 2, MATRIX_OF_DOUBLE_DATATYPE, &ns, &ns, &l2);
}
if (nsheets != 0)
{
/* Create a typed list to return the properties */
CreateVarFromPtr(Rhs + 3, MATRIX_OF_STRING_DATATYPE, &one, &nsheets, Sheetnames);
freeArrayOfString(Sheetnames, nsheets);
CreateVar(Rhs + 4, MATRIX_OF_DOUBLE_DATATYPE, &one, &nsheets, &l2);
for (i = 0; i < nsheets; i++)
{
*stk(l2 + i) = Abspos[i];
}
if (Abspos)
{
FREE(Abspos);
Abspos = NULL;
}
}
else
{
CreateVar(Rhs + 3, MATRIX_OF_DOUBLE_DATATYPE, &nsheets, &nsheets, &l2);
CreateVar(Rhs + 4, MATRIX_OF_DOUBLE_DATATYPE, &nsheets, &nsheets, &l2);
}
LhsVar(1) = Rhs + 1;
LhsVar(2) = Rhs + 2;
LhsVar(3) = Rhs + 3;
LhsVar(4) = Rhs + 4;
PutLhsVar();
return 0;
}
int main(int argc, char *argv[]) {
xlsWorkBook* pWB;
xlsWorkSheet* pWS;
unsigned int i;
int justList = 0;
char *sheetName = "";
if(argc < 2) {
Usage(argv[0]);
}
optind = 2; // skip file arg
int ch;
while ((ch = getopt(argc, argv, "lt:e:q:f:")) != -1) {
switch (ch) {
case 'l':
justList = 1;
break;
case 'e':
encoding = strdup(optarg);
break;
case 't':
sheetName = strdup(optarg);
break;
case 'q':
stringSeparator = optarg[0];
break;
case 'f':
fieldSeparator = strdup(optarg);
break;
default:
Usage(argv[0]);
break;
}
}
struct st_row_data* row;
WORD cellRow, cellCol;
// open workbook, choose standard conversion
pWB = xls_open(argv[1], encoding);
if (!pWB) {
fprintf(stderr, "File not found");
fprintf(stderr, "\n");
return EXIT_FAILURE;
}
// check if the requested sheet (if any) exists
if (sheetName[0]) {
for (i = 0; i < pWB->sheets.count; i++) {
if (strcmp(sheetName, pWB->sheets.sheet[i].name) == 0) {
break;
}
}
if (i == pWB->sheets.count) {
fprintf(stderr, "Sheet \"%s\" not found", sheetName);
fprintf(stderr, "\n");
return EXIT_FAILURE;
}
}
// process all sheets
for (i = 0; i < pWB->sheets.count; i++) {
int isFirstLine = 1;
// just looking for sheet names
if (justList) {
printf("%s\n", pWB->sheets.sheet[i].name);
continue;
}
// check if this the sheet we want
if (sheetName[0]) {
if (strcmp(sheetName, pWB->sheets.sheet[i].name) != 0) {
continue;
}
}
// open and parse the sheet
pWS = xls_getWorkSheet(pWB, i);
xls_parseWorkSheet(pWS);
// process all rows of the sheet
for (cellRow = 0; cellRow <= pWS->rows.lastrow; cellRow++) {
int isFirstCol = 1;
row = xls_row(pWS, cellRow);
// process cells
if (!isFirstLine) {
printf("%s", lineSeparator);
} else {
isFirstLine = 0;
}
for (cellCol = 0; cellCol <= pWS->rows.lastcol; cellCol++) {
//printf("Processing row=%d col=%d\n", cellRow+1, cellCol+1);
xlsCell *cell = xls_cell(pWS, cellRow, cellCol);
if ((!cell) || (cell->isHidden)) {
continue;
}
if (!isFirstCol) {
printf("%s", fieldSeparator);
} else {
isFirstCol = 0;
}
// display the colspan as only one cell, but reject rowspans (they can't be converted to CSV)
if (cell->rowspan > 1) {
fprintf(stderr, "Warning: %d rows spanned at col=%d row=%d: output will not match the Excel file.\n", cell->rowspan, cellCol+1, cellRow+1);
}
// display the value of the cell (either numeric or string)
if (cell->id == 0x27e || cell->id == 0x0BD || cell->id == 0x203) {
OutputNumber(cell->d);
} else if (cell->id == 0x06) {
// formula
if (cell->l == 0) // its a number
{
OutputNumber(cell->d);
} else {
if (!strcmp(cell->str, "bool")) // its boolean, and test cell->d
{
OutputString((int) cell->d ? "true" : "false");
} else if (!strcmp(cell->str, "error")) // formula is in error
{
OutputString("*error*");
} else // ... cell->str is valid as the result of a string formula.
{
OutputString(cell->str);
}
}
} else if (cell->str != NULL) {
OutputString(cell->str);
} else {
OutputString("");
}
}
}
xls_close_WS(pWS);
}
xls_close(pWB);
return EXIT_SUCCESS;
}
int process(char *filename) {
xlsWorkBook* pWorkbook;
xlsWorkSheet* pSheet;
pWorkbook = xls_open(filename, "iso-8859-15//TRANSLIT");
if ( pWorkbook == NULL ) {
fprintf(stderr, "Couldn't find %s.\n", filename);
return EXIT_FAILURE;
}
// Iterate over the sheets
for (int i = 0; i < pWorkbook->sheets.count; i++) {
char *name = (char *)pWorkbook->sheets.sheet[i].name;
char *filename = malloc(strlen(name)+5); // null and .csv
sprintf(filename, "%s.csv", slugify(name));
FILE *fd = fopen(filename, "w");
pSheet = xls_getWorkSheet(pWorkbook, i);
xls_parseWorkSheet(pSheet);
for (int cellRow = 0; cellRow <= pSheet->rows.lastrow; cellRow++) {
int isFirstCol = 1;
for (int cellCol = 0; cellCol <= pSheet->rows.lastcol; cellCol++) {
xlsCell *cell = xls_cell(pSheet, cellRow, cellCol);
if ((!cell) || (cell->isHidden)) {
continue;
}
if (!isFirstCol) {
fprintf(fd, "%s", fieldSeparator);
} else {
isFirstCol = 0;
}
if (cell->rowspan > 1) {
fprintf(stderr, "Warning: %d rows spanned at col=%d row=%d: output will not match the Excel file.\n", cell->rowspan, cellCol+1, cellRow+1);
}
// display the value of the cell (either numeric or string)
if (cell->id == 0x27e || cell->id == 0x0BD || cell->id == 0x203) {
fprintf(fd, "%.15g", cell->d);
} else if (cell->id == 0x06) {
if (cell->l == 0) {
fprintf(fd, "%.15g", cell->d);
} else {
/* Handle macros */
if (!strcmp((char *)cell->str, "bool")) {
write_string(fd, (int) cell->d ? "True" : "False");
} else if (!strcmp((char *)cell->str, "error")) {
write_string(fd, "*error*");
} else {
write_string(fd, (char *)cell->str);
}
}
} else if (cell->str != NULL) {
write_string(fd, (char *)cell->str);
} else {
write_string(fd, "");
}
}
fprintf(fd, "\n");
}
fclose(fd);
free(filename);
}
xls_close(pWorkbook);
return EXIT_SUCCESS;
}
void LoadTestLimits()
{
xlsWorkBook* pWB;
xlsWorkSheet* pWS;
int SheetCount, r, c, i, j, temp;
struct st_row::st_row_data* row ;
int debug_externel_limits = 1;
int index, offset;
pWB=xls_open("..\\Function54RefCode\\TestLimits.xls","UTF-8");
if (pWB!=NULL){
for (i = 0 ; i<pWB->sheets.count ; i++) {
if (debug_externel_limits){
printf("Sheet N%i (%s) pos %i\n", i, pWB->sheets.sheet[i].name,
pWB->sheets.sheet[i].filepos);
}
for (j=0; j < pWB->sheets.count; j++) {
if (!strcmp(pWB->sheets.sheet[i].name, SheetName[j])) {
//printf("Start to parse limits%s(%d)\n", SheetName[j], j);
pWS=xls_getWorkSheet(pWB,i);
xls_parseWorkSheet(pWS);
switch (j) {
case 0: //"FullRawCapacitance"
for (r = 0; r < TxChannelCount; r++) {
for (c = 0; c < RxChannelCount; c++) {
row = &pWS->rows.row[r+1];
if (debug_externel_limits) {
//printf("FullRawCapacitance L [%d][%d]%f\n",r,c, pWS->rows.row[r+1].cells.cell[2*c+1].d);
//printf("fullrawcapacitance l %f\n", (&row->cells.cell[2*c+1])->d);
//printf("fullrawcapacitance h %f\n", (&row->cells.cell[2*c+2])->d);
}
LowerImageLimit[r][c] = (&row->cells.cell[2*c+1])->d;
UpperImageLimit[r][c] = (&row->cells.cell[2*c+2])->d;
}
}
break;
case 1: //AdcRange
for (r = 0; r < TxChannelCount; r++) {
for (c = 0; c < RxChannelCount; c++) {
row = &pWS->rows.row[r+1];
ADCLowerImageLimit[r][c] = (&row->cells.cell[2*c+1])->d;
ADCUpperImageLimit[r][c] = (&row->cells.cell[2*c+2])->d;
}
}
break;
case 2: //SensorSpeed
for (r = 0; r < TxChannelCount; r++) {
for (c = 0; c < RxChannelCount; c++) {
row = &pWS->rows.row[r+1];
SensorSpeedLowerImageLimit[r][c] = (&row->cells.cell[2*c+1])->d;
SensorSpeedUpperImageLimit[r][c] = (&row->cells.cell[2*c+2])->d;
}
}
break;
case 3: //TRxOpen
row = &pWS->rows.row[1];
for(r = 0; r < TRX_mapping_max ; r++){
temp = TRxPhysical[r];
if ((TRxPhysical[r] != 0xFF) && ((&row->cells.cell[temp + 1])->d > 0)){
index = temp / 8;
offset = 7- (temp % 8);
TRX_Open[index] = TRX_Open[index] + pow(2.0,offset);
}
}
break;
case 4: //TRxGround
row = &pWS->rows.row[1];
for(r = 0; r < TRX_mapping_max ; r++){
temp = TRxPhysical[r];
if ((TRxPhysical[r] != 0xFF) && ((&row->cells.cell[temp + 1])->d > 0)){
index = temp / 8;
offset = 7- (temp % 8);
TRX_Gnd[index] = TRX_Gnd[index] + pow(2.0,offset);
//printf("trx, %d, row , %3.0f \n",TRxPhysical[r] , (&row->cells.cell[temp + 1])->d );
//printf("TRX_G1[%d]\n", TRX_Gnd[2]);
}
}
break;
case 5: //TRxToTRxShort
row = &pWS->rows.row[1];
for(r = 0; r < TRX_mapping_max ; r++){
temp = TRxPhysical[r];
if ((TRxPhysical[r] != 0xFF) && ((&row->cells.cell[temp + 1])->d > 0)){
index = temp / 8;
offset = 7- (temp % 8);
TRX_Short[index] = TRX_Short[index] + pow(2.0,offset);
}
}
break;
case 6: //"HighResistance"
r = 1;
for (c = 0; c < 3 ; c++) {
row=&pWS->rows.row[r];
HighResistanceLowerLimit[c] = (float)(&row->cells.cell[2*c+1])->d;
HighResistanceUpperLimit[c] = (float)(&row->cells.cell[2*c+2])->d;
}
break;
case 7: //MaxMinTest
break;
case 8://AbsADCRange
break;
case 9: //AbsDelta
break;
case 10: //AbsRaw
break;
default:
printf("[%d] Invalid value!\n", i);
break;
}
}
}
}
} else {
//Load TRX_Gnd test limit according to Physical TRx mapping.
for (int k = 0; k < TRX_mapping_max; k++){
temp = TRxPhysical[k];
if ( temp != 0xFF){
index = temp / 8;
offset = 7- (temp % 8);
TRX_Gnd[index] = TRX_Gnd[index] + pow(2.0,offset);
//printf (" TRxPhysical[%d],Index [%d], offset [%d], TRX_Gnd[%d], [%3.0f]\n", TRxPhysical[k],index,offset, TRX_Gnd[index], pow(2.0,offset) );
}
}
printf("Open file failed, use default Test Limit!\n");
}
return;
}
#include <libxls/xls.h>
}
//------------------------------------------------------------------------
CONCEPT_DLL_API ON_CREATE_CONTEXT MANAGEMENT_PARAMETERS {
return 0;
}
//------------------------------------------------------------------------
CONCEPT_FUNCTION_IMPL(xls_getVersion, 0)
RETURN_STRING(xls_getVersion())
END_IMPL
//------------------------------------------------------------------------
CONCEPT_FUNCTION_IMPL(xls_open, 2)
T_STRING(xls_open, 0)
T_STRING(xls_open, 1)
RETURN_NUMBER((long)xls_open(PARAM(0), PARAM(1)))
END_IMPL
//------------------------------------------------------------------------
CONCEPT_FUNCTION_IMPL(xls_close, 1)
T_HANDLE(xls_close, 0)
xlsWorkBook * wb = (xlsWorkBook *)PARAM_INT(0);
xls_close(wb);
RETURN_NUMBER(0);
END_IMPL
//------------------------------------------------------------------------
CONCEPT_FUNCTION_IMPL(xls_parseWorkBook, 1)
T_HANDLE(xls_parseWorkBook, 0)
xlsWorkBook * wb = (xlsWorkBook *)PARAM_INT(0);
xls_parseWorkBook(wb);
RETURN_NUMBER(0);