[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

Exporting to CSV with Excel formatting

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.

csvBuilder.Append("DITRONICS CHECK DETAIL REPORT");
    csvBuilder.Append(Environment.NewLine);
    csvBuilder.Append(Environment.NewLine);
    csvBuilder.Append(Environment.NewLine);
    csvBuilder.Append("DateNTime;");
    csvBuilder.Append("CheckAmount;");
    csvBuilder.Append("CheckType;");
    csvBuilder.Append("RouteAccountCheck;");
    csvBuilder.Append("CustomerName;");
    csvBuilder.Append("OverRide;");
    csvBuilder.Append("Status;");
    csvBuilder.Append(Environment.NewLine);



0
svasilakos
Asked:
svasilakos
1 Solution
 
DhaestCommented:
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 =
Excel.XlHAlign.xlHAlignCenter;

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 =
"£###,###,##0.00";

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,
Type.Missing,Type.Missing);
0
 
gnoonCommented:
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 http://www.codeproject.com/office/excelxmlspreadsheet.asp.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now