none
Excel formatting

    Question

  • Hi,

    I have an excel file (.xls) and I want to format it( adding borders, setting columns width, etc), but I want to do this without Excel. By this I mean to not have Excel installed on the  machine in order to run the program.

    I tried this one:

    Excel Xml Writer Library.

    But when trying to open the xls files it gave an error, so I want to use another one.

    Which free technology can I use?

    Thanks.


    pyram



    • Edited by pyram07 Wednesday, January 23, 2013 4:43 AM
    Wednesday, January 23, 2013 4:05 AM

Answers

  • You can have a try at NPOI. Please refer to http://npoi.codeplex.com/


    mark kang

    • Marked as answer by pyram07 Wednesday, January 23, 2013 3:02 PM
    Wednesday, January 23, 2013 5:44 AM
  • try this

    http://www.carlosag.net/tools/excelxmlwriter/generator

    • Marked as answer by pyram07 Wednesday, January 23, 2013 3:02 PM
    Wednesday, January 23, 2013 5:56 AM
  • When you speak of "Excel Sheet" do you mean a .xls (pre-2007 format) or .xlsx (the new fomrat used since Excel 2007)?

    I ask because the later is nothing more then a bunch of XML files in a zip-container. (You can check that by renaming a *.xlsx to *.zip and view it's contents.)
    And modifying a few XML file(s) in a zip container is many times easier then doing work with the older Excel Sheet Formats.

    • Marked as answer by pyram07 Wednesday, January 23, 2013 3:02 PM
    Wednesday, January 23, 2013 1:16 PM
  • Add Borders:

                //Load Excel
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"..\..\test.xls");
                Worksheet sheet = workbook.Worksheets[0];
                 
                //Add Borders
                sheet.Range["A1:E15"].Borders.LineStyle = LineStyleType.Double;
                sheet.Range["A1:E15"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
                sheet.Range["A1:E15"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
                sheet.Range["A1:E15"].Borders.Color = Color.DeepSkyBlue;

    Setting Column Width:

                sheet.SetColumnWidth(3, 18);
    I use a .NET Excel component without MS Excel.

    • Marked as answer by pyram07 Friday, January 25, 2013 6:10 PM
    Thursday, January 24, 2013 2:33 AM

All replies

  • You can have a try at NPOI. Please refer to http://npoi.codeplex.com/


    mark kang

    • Marked as answer by pyram07 Wednesday, January 23, 2013 3:02 PM
    Wednesday, January 23, 2013 5:44 AM
  • try this

    http://www.carlosag.net/tools/excelxmlwriter/generator

    • Marked as answer by pyram07 Wednesday, January 23, 2013 3:02 PM
    Wednesday, January 23, 2013 5:56 AM
  • When you speak of "Excel Sheet" do you mean a .xls (pre-2007 format) or .xlsx (the new fomrat used since Excel 2007)?

    I ask because the later is nothing more then a bunch of XML files in a zip-container. (You can check that by renaming a *.xlsx to *.zip and view it's contents.)
    And modifying a few XML file(s) in a zip container is many times easier then doing work with the older Excel Sheet Formats.

    • Marked as answer by pyram07 Wednesday, January 23, 2013 3:02 PM
    Wednesday, January 23, 2013 1:16 PM
  • Thank you all!

    pyram

    Wednesday, January 23, 2013 3:02 PM
  • Add Borders:

                //Load Excel
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"..\..\test.xls");
                Worksheet sheet = workbook.Worksheets[0];
                 
                //Add Borders
                sheet.Range["A1:E15"].Borders.LineStyle = LineStyleType.Double;
                sheet.Range["A1:E15"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
                sheet.Range["A1:E15"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
                sheet.Range["A1:E15"].Borders.Color = Color.DeepSkyBlue;

    Setting Column Width:

                sheet.SetColumnWidth(3, 18);
    I use a .NET Excel component without MS Excel.

    • Marked as answer by pyram07 Friday, January 25, 2013 6:10 PM
    Thursday, January 24, 2013 2:33 AM
  • Hi, try this:

    // Load excel file.
    var file = ExcelFile.Load("Book1.xls");
    var sheet = file.Worksheets.ActiveWorksheet;
    
    // Set borders.
    var cell = sheet.Cells["A1"];
    cell.Style.Borders.SetBorders(MultipleBorders.Bottom, Color.Blue, LineStyle.Double);
    var range = sheet.Cells.GetSubrange("A3", "A10");
    range.Style.Borders.SetBorders(MultipleBorders.Outside, Color.Blue, LineStyle.Dotted);
    
    // Set column width to 3 inch.
    int columnWidth = (int)LengthUnitConverter.Convert(3, LengthUnit.Inch, LengthUnit.ZeroCharacterWidth256thPart);
    sheet.Columns["A"].Width = columnWidth;
    
    // Save excel file.
    file.Save("Book2.xls");

    The code uses a free version of this C# Excel DLL. 

    Thursday, November 28, 2013 7:30 AM