Exporting to CSV with Excel formatting

Posted on 2007-08-08
Last Modified: 2013-11-07
I'm new to C# and I am using csvBuilder to export data to an excel file. Is their a way to format the export so it passes control characters to excel for things like bolding text or even changing the color of text. Or is their another function I could use that is  excel specific. Basically I have the ability to export CSV for my customers who want to import to excel but I'd love to take in one step further and format the file for them.

Just for grins here is a subset of the code I am using now.


Question by:svasilakos
    LVL 53

    Accepted Solution

    A CSV-file doesn't support formatting. It just a plain text-format.
    Do you want to save it directly into an excel-file ? Then this can help you on your way:
    Excel.Application ExcelApp;
    Excel._Workbook ExcelWorkbook;
    Excel._Worksheet ExcelWorkSheet;

    ExcelApp = new Excel.Application();
    ExcelApp.Visible = true;
    ExcelWorkbook = (Excel._Workbook)(ExcelApp.Workbooks.Add(
    Missing.Value ));

    ExcelWorkSheet = (Excel._Worksheet)ExcelWorkbook.Sheets["Sheet1"];
    ExcelWorkSheet.Name = "My Sheet";

    //Make sure Excel is visible and give the user control
    //of Microsoft Excel's lifetime.
    ExcelApp.Visible = true;
    ExcelApp.UserControl = true;

    //text in cell
    ExcelWorkSheet.Cells[1, 1] = "test sheet";
    ExcelWorkSheet.Cells[3, 1] = "test1";
    ExcelWorkSheet.Cells[3, 2] = "test2";
    ExcelWorkSheet.Cells[3, 3] = "test3";
    ExcelWorkSheet.Cells[3, 4] = "test4";
    ExcelWorkSheet.Cells[3, 5] = "test5";
    ExcelWorkSheet.Cells[3, 6] = " test6";

    ExcelWorkSheet.get_Range("A3", "F3").HorizontalAlignment =

    ExcelWorkSheet.get_Range("A1", "A1").Font.Bold = true;
    ExcelWorkSheet.get_Range("A3", "F3").EntireColumn.AutoFit();
    ExcelWorkSheet.get_Range("A3",F3").BorderAround(Excel.XlLineStyle.xlContinuous,Exce l.XlBorderWeight.xlMedium,
    Excel.XlColorIndex.xlColorIndexAutomatic,Excel.XlC olorIndex.xlColorIndexAutomatic);

    ExcelWorkSheet.get_Range("E1",E1").Columns.EntireColumn.NumberFormat =

    string savepath;
    savepath = Application.ExecutablePath.ToString();
    savepath = savepath.Substring(0,savepath.LastIndexOf("\\"));
    savepath = savepath + "\\TestExcelFile.xls";

    ExcelWorkbook.SaveAs(savepath,Excel.XlFileFormat.x lCSV,Type.Missing,Type.Missing,Type.Missing,
    Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange,T ype.Missing,Type.Missing,
    LVL 16

    Expert Comment

    Dhaest is right, CSV is just a plain text of data without formatting informations.
    But using the COM Interop may not be a good solution; undocumented API, memory management, slow process, license, version etc.

    If your final result is a plain text & familiar with excel, you may go with XML Spreadsheet file format solution. With the format, you can write file as a text file (faster & no more DLL needed) but need a little bit knowledge about the XML syntax. Everything can be learned. You just save your Excel file (with formatted cells) using 'Save As..' and select XML Spreadsheet, then open the XML file with Internet Explorer to see its syntax. You can learn from there!

    For more information, go here

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now