CODEWITHSUNDEEP
c#wpfwinforms
simba
simba

Reputation: 463

Exporting data to excel from grid

I am exporting data from WPF datagrid to an excel worksheet in my application. Here is the code snippet i arrived at.

dgDisplay.SelectAllCells();
dgDisplay.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, dgDisplay);
//String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
String result = (string)Clipboard.GetData(DataFormats.Text);
dgDisplay.UnselectAllCells();
string path = Environment.CurrentDirectory + "\\Reports\\SampleReport.xls";
System.IO.StreamWriter file1 = new System.IO.StreamWriter(@path);
file1.WriteLine(result.Replace(',', ' '));
file1.Close();

Now the excel file is created fine with expected data. But on opening via code or by using excel. i get the below warning when opening

enter image description here

Though there might be other reasons for a similar error frome excel this one i think is due to the below 2 lines

String result = (string)Clipboard.GetData(DataFormats.Text);

This changes the format. If i select Yes to proceed and open it gives me the correct data. I have been trying to figure out a way so that the data generated is same but does not pop a warning for changed format. Please suggest ?

Upvotes: 0

Views: 1335

Answers (4)

woresa
woresa

Reputation: 3

I use the code, but each line is stored in one cell (A1) with comma separting each fields

but When i use GetData(DataFormats.Text), every field has one column, but before Excel show it warning.

Upvotes: 0

simba
simba

Reputation: 463

Here is the code that worked for me. Updating as asked.

private void StartCreatingReport(DataGrid dgDisplay)
    {

        dgDisplay.SelectAllCells();
        dgDisplay.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
        ApplicationCommands.Copy.Execute(null, dgDisplay);
        String result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);           
        dgDisplay.UnselectAllCells();

        string path = Environment.CurrentDirectory + "\\Reports\\SampleReport.csv";



        System.IO.StreamWriter file1 = new System.IO.StreamWriter(@path);

        file1.WriteLine(result);
        file1.Close();
    }

I pass any grid that has to be exported to excel to this method. I also place a csv file in path (SampleReport.csv in this case)

Upvotes: 0

Joachim Rosskopf
Joachim Rosskopf

Reputation: 1269

The .xls-extension you give your file is reserved for the binary Excel (BIFF8) format. You are saving a delimited text-file, which should have the extension .csv. Try to change the file-name and it should work.

If you want to generate a file in the properitary, binary Excel format you have to do manual work and use a library, e.g. ExcelLibrary.

Upvotes: 1

Raidri
Raidri

Reputation: 17550

You cannot write simple text to an Excel .xlsx file.

Option 1: You write a .csv file and open that in Excel.

Option 2: You use a libraby to write correct .xlsx files, e.g. ClosedXML

Upvotes: 1

Related Questions