Convert HTML tables with cells merged with complex headers to DataTable and export Excel

Steps:

I. Foreground JS retrieves HtmlTable data and Concatenates the data according to the set delimiter

Data = "" # divRptTable "). find ("table "). find ("tr "). each (tr = $ ("td "). each (td = $ (rowspan = td. attr ("rowspan ")? Td. attr ("rowspan"): 1 colspan = td. attr ("colspan ")? Td. attr ("colspan"): 1 = data + td. text () + "# {valueSeparater}" + rowspan + "# {valueSeparater}" + colspan + "# {columnSeparater}" = data + "# {rowSeparater}" "# hidTableData" year = $ ("select [name = 'Year']"). find ("option [value = '" + $ ("select [name = 'Year']"). val () + "']" sem = $ ("select [name = 'sem']"). find ("option [value = '" + $ ("select [name = 'sem']"). val () + "']" scl = $ ("select [name = 'school']"). find ("option [value = '" + $ ("select [name = 'school']"). val () + "']" grade = $ ("select [name = 'grad']"). find ("option [value = '" + $ ("select [name = 'grad']"). val () + "']" exam = $ ("select [name = 'exam']"). find ("option [value = '" + $ ("select [name = 'exam']"). val () + "']" "# hidTitle "). val (scl + "" + grade + "" + year + "" + sem + "" + "# frm" </script>View Code

2. The background first converts the string passed by the foreground into an HtmlTable object, and then converts it to a able object.

Variable rowSeparater = columnSeparater = valueSeparater = nullFlag = string to HtmlTable String2HtmlTable (= [] rowArray = data. split (row rowArray) = [] colArray = row. split (col colArray) = [] valArr = col. split (val = valArr [rowspan =. parse (valArr [colspan =. parse (valArr [=== HtmlTable to DataTable = DataTable columns colCount = (htmlTable. rows. count> (HtmlTableCell htmlTableCell htmlTable. rows [+ = DataTable row rowCount = add columns to the DataTable (I =; I <colCount; I ++ Add rows to the DataTable (I =; I <rowCount; I ++) = (j =; j <colCount; j ++) = Conversion (I =; I 3. Export DataTable to Excel

Generate Excel CreateExcel (DataTable dataTable, =. IsNullOrEmpty (dataTable. TableName )? Workbook. CreateSheet (): workbook. CreateSheet (dataTable. TableName); Title (! (DataTable. columns. count>] [] = dataTable. tableName + HtmlTableUtil. valueSeparater ++ HtmlTableUtil. valueSeparater + (I =; I <dataTable. columns. count; I ++] [I] = HtmlTableUtil. nullFlag + HtmlTableUtil. valueSeparater ++ HtmlTableUtil. valueSeparater + fill data (I =; I <dataTable. rows. count; I ++) = sheet. createRow (I); row. heightInPoints = (j =; j <dataTable. columns. count; j ++) cellValue = row. CreateCell (j); ICellStyle cellStyle ===== [] valArr = cellValue. Split (valArr []! = (. TryParse (valArr [],]); (I! =) CellStyle. Alignment = (I! = UnitWidth = oldWidth = width = () Math. Round (CommonHelper. GetWidthUnitCount (valArr []) * = width> unitWidth *? UnitWidth * = width <unitWidth *? UnitWidth * (width> merge cells (I =; I <dataTable. rows. count; I ++) = (j =; j <dataTable. columns. count; j ++) cellValue = [] valArr = cellValue. split (rowSpan =. parse (valArr [colSpan =. parse (valArr [(rowSpan> | colSpan> NPOI. SS. util. cellRangeAddress (I, I + rowSpan-, j, j + colSpan-output to Excel = (FileStream fs = [] bArr =View Code