FlexGrid excel file import problem

Posted by: dasun.suranjaya on 17 November 2020, 8:21 am EST

  • Posted 17 November 2020, 8:21 am EST - Updated 3 October 2022, 6:11 am EST

    Hi,

    I’m using flexgrid as a data grid component in a react application. Following is the flexgrid declaration.

    <wjcGrid.FlexGrid  itemsSource={this.state.data}  initialized={this.initializeFlexSheet.bind(this)} >
                        <wjcGrid.FlexGridColumn header="ID" binding="id"></wjcGrid.FlexGridColumn>
                        
                        <wjcGrid.FlexGridColumn header="Country" binding="country" dataMap={this.state.countryMap} ></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Product" binding="product"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Amount" binding="amount" format="c" aggregate="Sum"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Pending" binding="amount2" format="c2" aggregate="Sum"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Discount" binding="discount" format="p1" aggregate="Avg"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Active" binding="active" width={70}></wjcGrid.FlexGridColumn>
                    </wjcGrid.FlexGrid>
    

    as you can see there is a check box column and drop down menu column in the grid.

    I’m having an excel file import/export functionality bounded to this grid.

    
    let fileInput = document.getElementById("importFile");
            console.log("file content :"+fileInput.files[0]);
            wjcGridXlsx.FlexGridXlsxConverter.loadAsync(this.flex, fileInput.files[0], { includeColumnHeaders: this.state.includeColumnHeader });
    
    
    

    when come to the excel file import functionality content of the excel file is loading to the grid but drop down menus and check boxes are no longer displayed in corresponding grid cells. Instead only the values in excel file is displaying. Please refer the attachments

    Following is the complete code

    import "@grapecity/wijmo.styles/wijmo.css";
    import "bootstrap.css";
    import "./app.css";
    //
    import * as React from 'react';
    import * as ReactDOM from 'react-dom';
    //
    import * as wjcCore from "@grapecity/wijmo";
    import * as wjcGrid from "@grapecity/wijmo.react.grid";
    import * as wjGrid from "@grapecity/wijmo.grid";
    import * as wjcXlsx from "@grapecity/wijmo.xlsx";
    import * as wjcGridXlsx from "@grapecity/wijmo.grid.xlsx";
    import { getProductOrders } from "./data";
    class App extends React.Component {
        constructor(props) {
            super(props);
            this.state = {
                data: getProductOrders(5),
                fileName: "",
                includeColumnHeader: true,
                customContent: false,
                //countryMap : ['US', 'Germany', 'UK', 'Japan', 'Italy', 'Greece']
                countryMap : ['us', 'uk', 'japan']
            };
        }
        render() {
    
    console.log("data==="+this.state.data[0].country);
    console.log("data==="+this.state.data[0].active);
    
            return <div className="container-fluid">
                <div className="row">
                    
                    <wjcGrid.FlexGrid  itemsSource={this.state.data}  initialized={this.initializeFlexSheet.bind(this)} >
                        <wjcGrid.FlexGridColumn header="ID" binding="id"></wjcGrid.FlexGridColumn>
                        
                        <wjcGrid.FlexGridColumn header="Country" binding="country" dataMap={this.state.countryMap} ></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Product" binding="product"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Amount" binding="amount" format="c" aggregate="Sum"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Pending" binding="amount2" format="c2" aggregate="Sum"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Discount" binding="discount" format="p1" aggregate="Avg"></wjcGrid.FlexGridColumn>
                        <wjcGrid.FlexGridColumn header="Active" binding="active" width={70}></wjcGrid.FlexGridColumn>
                    </wjcGrid.FlexGrid>
                </div>
                <div className="row">
                    <div className="col-md-6 col-xs-12">
                        <div className="form-inline well well-lg">
                            <input type="file" className="form-control" style={{ width: '250px' }} id="importFile" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel.sheet.macroEnabled.12"/>
                        <button onClick={this.load.bind(this)} className="btn btn-default">Import</button>
                           {/*  <div className="checkbox">
                                <label>
                                    <input defaultChecked type="checkbox"/> Include Column Header
                                </label>
                            </div> */}
                        </div>
                    </div>
                    <div className="col-md-6 col-xs-12">
                        <div className="form-inline well well-lg">
                            <button onClick={this.save.bind(this)} className="btn btn-default">Export</button>
                           {/*  <div className="checkbox">
                                <label>
                                    <input defaultChecked type="checkbox"/> Include Column Header
                            </label>
                            </div> */}
                            {/* <div className="checkbox">
                                <label>
                                    <input v-model="customContent" type="checkbox"/> Custom Cell Content
                            </label>
                            </div> */}
                        </div>
                    </div>
                </div>
            </div>;
        }
        initializeFlexSheet(flex) {
            this.flex = flex;
           
        }
        save() {
            wjcGridXlsx.FlexGridXlsxConverter.saveAsync(this.flex, {
                includeColumnHeaders: this.state.includeColumnHeader,
                includeCellStyles: false,
                formatItem: this.state.customContent ? this.state._exportFormatItem : null
            }, "FlexGrid.xlsx");
        }
        load() {
            let fileInput = document.getElementById("importFile");
            console.log("file content :"+fileInput.files[0]);
            wjcGridXlsx.FlexGridXlsxConverter.loadAsync(this.flex, fileInput.files[0], { includeColumnHeaders: this.state.includeColumnHeader });
          //wjcGridXlsx.FlexGridXlsxConverter.load(this.flex, fileInput.files[0], { includeColumnHeaders: this.state.includeColumnHeader });
            this.setState({
               //data: getProductOrders(500)
               //customContent: true,
               //data: this.state.data
                
                
            });
          
        }
        exportFormatItem(args) {
            var p = args.panel, row = args.row, col = args.col, xlsxCell = args.xlsxCell, cell, color;
            if (p.cellType === wjGrid.CellType.Cell) {
                if (p.columns[col].binding === "color") {
                    //color = p.rows[row].dataItem['color'];
                    if (xlsxCell.value) {
                        if (!xlsxCell.style.font) {
                            xlsxCell.style.font = {};
                        }
                        xlsxCell.style.font.color = xlsxCell.value.toLowerCase();
                    }
                }
                else if (p.columns[col].binding === "active" &&
                    p.rows[row] instanceof wjGrid.GroupRow) {
                    cell = args.getFormattedCell();
                    xlsxCell.value = cell.textContent.trim();
                    xlsxCell.style.hAlign = wjcXlsx.HAlign.Left;
                }
            }
        }
    }
    ReactDOM.render(<App />, document.getElementById('app'));
    
    

    Please help me to resolve this problem.

    Thanks,

    Dasun

  • Posted 17 November 2020, 11:40 pm EST

    Hi Dasun,

    As the default functionality, when the excel file is loaded into the FlexGrid, it is loaded in the unbound mode and columns get reset which is why DataMaps and checkboxes are lost. If you want to preserve the columns layout then you need to map the workbooks data to JSON and assign it as data source. Please refer to the following sample which demonstrates the same and let us know if you face any issues:

    https://codesandbox.io/s/wijmo-react-forked-d2tud?file=/src/App.js

    Regards

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels