none
C# how to write data to excel? RRS feed

  • Question

  • - I cannot to use Interop excel in this project.

    - So I try to find another way to do this.

    commandStr = "SELECT * FROM [" + excelSheets[i] + "]";
    command = new OleDbCommand(commandStr, connection);
    OleDbDataReader reader = command.ExecuteReader();
    
    Regex regex0 = new Regex(@ "\d\.\d{3}");
    Regex regex1 = new Regex(@ "\d{1,3}[,]\d");
    
    while (reader.Read()) {
     string strTemp, strAfter;
     StringBuilder strBuilder = new StringBuilder();
    
     for (i = 1; i < reader.VisibleFieldCount; i++) {
      strTemp = reader[i].ToString();
    
      if (strTemp == string.Empty)
       continue;
      strBuilder.AppendLine(strTemp);
      if (regex0.IsMatch(strTemp)) {
       strAfter = strTemp.Replace(".", "");
       strAfter = strAfter.Replace(",", ".");
       continue;
      } else if (regex1.IsMatch(strTemp)) {
       strAfter = strTemp.Replace(',', '.');
       continue;
      }
     }
    }
    command.Dispose();

    - How can I write value to excel at the location reader[i]?

    Tuesday, July 3, 2018 6:35 AM

Answers

All replies

  • Hello,

    I would suggest looking at a library called SpreadSheetLight which is free, does not require Excel to be installed and has a help file along with sample code to work with. Only downside is the library only works with .xlsx not .xls.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, July 3, 2018 9:11 AM
    Moderator
  • - Well, actually my custommer used both type xls and xlsx. So It's not the solution for my task. Thanks anyway!
    Tuesday, July 3, 2018 10:24 AM
  • Hello,

    In that case the only libraries I recommend (this is a commercial libraries) are in cost order, Gembox.SpreadSheet and their free edition (has limitations), then Aspose Cells.

    My guess is you are looking for methods which are free as most developer want yet with a commercial libraries as mentioned above (and I've used both) in the end for projects that require .xls and/or .xlsx they work great and save you time attempting to figure out homebrewed code. Downside of course is the cost but personally speaking I'd rather spend my time not concerned about two different formats to write too and spend my time with other task.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, July 3, 2018 10:39 AM
    Moderator
  • - Finnaly I found this guy: NPOI

    - It work pretty well. Thanks!

    • Marked as answer by That'sMyName Tuesday, July 3, 2018 3:47 PM
    Tuesday, July 3, 2018 3:46 PM