cannot apply cell format change EXCEL when C# excel export

Question

I'm using Visual Studio 2008, MS OFFICE 2010.(at WIN 7)

When I make EXCEL file through my program, made in c#, cannot apply cell format change. But this issue is ONLY caused when make by STRING ARRAY. If input only one cell, worked well.(ex. _SRange["A0", "A0"].Value2 = "2018-02-13";)

Anybody help me? The code is like this.

       public void ExportOrderExcel(System.Data.DataTable dt, string Name, List<string> colNames)
    {
        System.Data.DataTable  manufacturedDt = this.ManufatureDtForExcel(dt, colNames);
        Application app = new ApplicationClass();

        if (app == null)
        {
            System.Windows.Forms.MessageBox.Show("not started EXCEL");
            return;
        }

        Workbooks workbooks = app.Workbooks;
        _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Sheets sheets = workbook.Worksheets;
        _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
        if (worksheet == null)
        {
            System.Windows.Forms.MessageBox.Show("not exist WORKSHEET");
        }

        int titleRowCnt = 1;
        int columnRowCnt = 1;
        int totalRowCnt=  manufacturedDt.Rows.Count + titleRowCnt + columnRowCnt;
        object startPoint = worksheet.Cells[1, 1];
        object endPoint = worksheet.Cells[(totalRowCnt), manufacturedDt.Columns.Count];
        _SRange = worksheet.get_Range(startPoint, endPoint);

        string[,] array1 = new string[totalRowCnt, manufacturedDt.Columns.Count];

        //set title
        _SRange.get_Range(GetCellName(0, 0), GetCellName(3, 0)).Merge(1);
        array1[0, 0] = Name;

        // column Name
        for (int colnum = 0; colnum < manufacturedDt.Columns.Count; colnum++)
        {
            array1[1, colnum] = manufacturedDt.Columns[colnum].Caption;
        }

        // set value
        for (int row = 2; row < manufacturedDt.Rows.Count + 2; row++)
        {
            for (int col = 0; col < manufacturedDt.Columns.Count; col++)
            {
                array1[row, col] = manufacturedDt.Rows[row - 2][col].ToString();
            }
        }

        // input cell
        _SRange.Value2 = array1;

        app.Visible = true;
    }

This is how you do it: https://stackoverflow.com/a/2294087/495455 — Jeremy Thompson, Feb 13 '18 at 02:18
It seems like different problem. :'( but I get hint from the link. Thank you! — Glenda Cheon, Feb 14 '18 at 02:04

score 0 · Accepted Answer · answered Feb 14 '18 at 03:10

0

Solved problem. 'String ARRAY' is the cause. String Array may export excel 'string type' only, so i can't apply cell format. I change my code string[,] > object[,].

answered Feb 14 '18 at 03:10

Glenda Cheon

3
3

cannot apply cell format change EXCEL when C# excel export

1 Answers1