saving data in csv file

I have a problem to save data in .csv file.

     void WriteLog(DataRow rzad)
    {
            StreamWriter sw = new StreamWriter("log.csv", true);
            int iColCount = 8;

            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(rzad[i]))
                {
                    sw.Write(rzad[i].ToString());
                    sw.Write("\t");
                }
            }
            sw.Write("\n");
            sw.Flush();
            sw.Close();
    }

The problem is tak in file I have data in A column. I want to smash one row in DataRow format to 8 parts, which are put in 8 开发者_Go百科different columns. My function working as it doesn't see the tab ("\t").

I cant post images so I try to describe results in csv file:

2011-03-17 14:34:11asdPrzekroczono krytyczną minimalną wymaganą wartość parametru5010050080550

This is my example row and I want to smash it to 8 columns:

2011-03-17 14:34:11     asd     Przekroczono krytyczną minimalną wymaganą wartość parametru   50     100    500     80      550     

"#\t#" doesn't help. The results is:

"2011-03-17 18:29:17#   #asd#   #Przekroczono krytyczną, maksymalną, wymaganą wartość parametru#    #560#   #100#   #500#   #80#    #550#   #"

There is some tabulation but my point is that was made no space but a transition to the next cell :(

"\u0008" also doesn't help.

---

First, you say you are writing to a CSV (comma seperated values) file. But, you are really writing to a tab-delimited file. And, you need to write /r/n between lines:

This works:

    StreamWriter sw = new StreamWriter(@"c:\log.csv", true); 
    int iColCount = 8; 
    for (int i = 0; i < iColCount; i++)
    {           
        {
            sw.Write(i.ToString()); 
            sw.Write("\t"); 
        } 
    } 
    sw.Write("\r\n"); 
    sw.Flush(); 
    sw.Close();

---

private ActionResult ExportMeasuresWithTabsFromClient(DataSet spendData,
                                                      string excelFileName,
                                                      bool isFirstColumnDateAndRestDouble = false
                                                      //int numberFormatStartingColumnNumber = -1,
                                                      //int dateFieldColumn = -1)
{
    var fileName = "Bulk Export" + Guid.NewGuid() + ".xlsx";
    // Checking whether the directly exist and save the file to server temp folder
    bool exists = System.IO.Directory.Exists(Server.MapPath("~/" + Resource.ExportLocationTemp));// Common temp location for export as it is not saving the file in the location
    if (!exists)
    {
        DirectoryInfo di = System.IO.Directory.CreateDirectory(Server.MapPath("~/" + Resource.ExportLocationTemp));
        if (di == null || !di.Exists)
        {
            var msg = $"{Resource.MP_CreateDirectory_Failed} '{Resource.ExportLocationTemp}'.";
            _log.Error(msg);
            throw new Exception(msg);
        }
    }

    var fullPath = Path.Combine(Server.MapPath("~/" + Resource.ExportLocationTemp), fileName);
    _log.Info("Entering ExportMeasuresWithTabsFromClient");
    //Write the workbook to a memory stream
    MemoryStream output = new MemoryStream();
    try
    {
        XSSFWorkbook workbook = new XSSFWorkbook();
        foreach (DataTable dt in spendData.Tables)
        {
            if (getOneWorksheet(workbook, dt))  //, isFirstColumnDateAndRestDouble, numberFormatStartingColumnNumber, dateFieldColumn))
                _log.Info($"Created worksheet for TableName: '{dt.TableName}'");
            else
                _log.Error($"Failed to create worksheet for TableName: '{dt.TableName}'");
        }

        using (FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(file);
        }

        //Return the result to the end user
        //TempData.Add("downloadStatus", 1);
        _log.Info("Exiting ExportMeasuresWithTabsFromClient");



    }
    catch (Exception ex)
    {
        _log.Error(ex.Message, ex);
        _log.Info("Error in ExportMeasuresWithTabsFromClient");
    }
    return Json(fileName);
    //Suggested file name in the "Save as" dialog which will be displayed to the end user
}

private bool getOneWorksheet(XSSFWorkbook workbook,
                             DataTable dataTable,
                             bool isFirstColumnDateAndRestDouble = false)
{
    _log.Info("Entering getOneWorksheet");
    try
    {
        //Create new Excel sheet
        //var sheet = workbook.CreateSheet(getSheetName(dataTable));
        var sheet = workbook.CreateSheet(dataTable.TableName);

        //Create a header row
        var headerRow = sheet.CreateRow(0);

        //(Optional) freeze the header row so it is not scrolled
        sheet.CreateFreezePane(0, 1, 0, 1);

        // Setting for the styles for edited cells
        ICellStyle styleEditableCells = workbook.CreateCellStyle();
        styleEditableCells.IsLocked = false;
        styleEditableCells.WrapText = true;

        // Setting for the styles for non edited cells
        ICellStyle styleForNonEditableCells = workbook.CreateCellStyle();
        styleForNonEditableCells.IsLocked = true;
        styleForNonEditableCells.FillPattern = FillPattern.AltBars;
        styleForNonEditableCells.FillBackgroundColor = NPOI.SS.UserModel.IndexedColors.Grey25Percent.Index;
        styleForNonEditableCells.WrapText = true;
        int rowNumber = 1;
        //Populate the sheet with values from the grid data.
        foreach (DataRow dataRow in dataTable.Rows)
        {
            //Create a new row in Excel sheet.
            var excelRow = sheet.CreateRow(rowNumber++);
            for (int column = 0; column < dataTable.Columns.Count; column++)
            {
                ICell cell = excelRow.CreateCell(column);
                if (isFirstColumnDateAndRestDouble) // if this true --> ROI -> SaveAndDownload button (only for that) 
                {
                    // Protecting the sheet and setting the styles (editing and non editing)
                    sheet.ProtectSheet(Resource.Modeling_Roi_SaveDonwloadexcelPassword);
                    if (column == 0 || column == 1)
                        cell.CellStyle = styleForNonEditableCells;
                    else
                        cell.CellStyle = styleEditableCells;
                }
                //Set values for the cells
                double value;
                if (double.TryParse(dataRow[column].ToString(), out value))
                    cell.SetCellValue(value);
                else
                    cell.SetCellValue(dataRow[column].ToString());
            }
        }
        for (int column = 0; column < dataTable.Columns.Count; column++) // Looping and wraping the column values
        {
            headerRow.CreateCell(column).SetCellValue(dataTable.Columns[column].ColumnName);
            sheet.AutoSizeColumn(column);
            if (sheet.GetColumnWidth(column) / 255 < 254)  /// DDE 1.5 6170 --> Handling the higher exponential value
            {
                sheet.SetColumnWidth(column, sheet.GetColumnWidth(column) + (2 * 256));
            }
        }
        return true;
    }
    catch (Exception ex)
    {
        _log.Error(ex.Message, ex);
        _log.Info("Error in getOneWorksheet");
    }
    return false;
}

---

I'm inclined to agree @Hossein. I think it's an encoding issue. I ran your code exactly as it is on my machine, and it worked perfectly.

---

Just in case you still haven't found a solution since you wrote this question a year ago:

You can use the KBCSV library which is very popular and handles pretty much everything you require. It uses csv by default but can be easily modified to handle tsv.

For a very simple and easily breakable solution you can try: string.Join("\t", rzad) + "\r\n"

This wouldn't take into account fields that contain tabs. If a field contains a tab, it will make this method useless. Typically fields that would contain the delimiter are double quoted and fields that contain double quotes would be double double quoted.

It's not too difficult to implement this, but it would be reinventing the wheel as it has Kent Boogart has already spent many hours taking into consideration a few of the edge cases that I would not be aware of.

---

The following function is used to write the csv file.

public static void WriteCSV(string file, string content)
{
   using (StreamWriter sw = new StreamWriter(file))
   {
      sw.Write(content);
   }
}

Then call this function by

string appendText = "";
for (int i = 0; i < iColCount; i++)
{
     if (!Convert.IsDBNull(rzad[i]))
     {
         appendText += appendText == "" ? rzad[i].toString() : "," + rzad[i].toString();
     }
}
WriteCSV("C:\\out\\out.csv",appendText);

Another simple example for multiple rows, each row is separated by a new line character '\n'.

WriteCSV("C:\\out\\out.csv","a,b,c,d,e,f,g\nh,i,j,k,l,m,n\n");

---

if (editLabelsDT != null)
                {
                    var workbook = new XSSFWorkbook();
                    var sheet = workbook.CreateSheet();
                    sheet.ProtectSheet(Resource.Input_ExportEditMeasureLabel);

                    ICellStyle styleEditableCells = workbook.CreateCellStyle();
                    styleEditableCells.IsLocked = false;
                    styleEditableCells.WrapText = true;

                    ICellStyle styleForNonEditableCells = workbook.CreateCellStyle();
                    styleForNonEditableCells.IsLocked = true;
                    styleForNonEditableCells.FillPattern = FillPattern.AltBars;
                    styleForNonEditableCells.FillBackgroundColor = NPOI.SS.UserModel.IndexedColors.Grey25Percent.Index;
                    styleForNonEditableCells.WrapText = true;

                    //Create a header row
                    var headerRow = sheet.CreateRow(0);
                    //(Optional) freeze the header row so it is not scrolled
                    sheet.CreateFreezePane(0, 1, 0, 1);
                    int rowNumber = 1;
                    //Populate the sheet with values from the grid data
                    foreach (DataRow dr in editLabelsDT.Rows)
                    {
                        //Create a new row
                        var row = sheet.CreateRow(rowNumber++);
                        for (int i = 0; i < editLabelsDT.Columns.Count; i++)
                        {
                            ICell cell1 = row.CreateCell(i);
                            if (i == 0)
                                cell1.CellStyle = styleForNonEditableCells;
                            else
                                cell1.CellStyle = styleEditableCells;
                            //Set values for the cells
                            cell1.SetCellValue(dr[i].ToString());
                        }
                    }

---

using (SqlDataAdapter sda = new SqlDataAdapter())
{
    cmd.Connection = con;
    sda.SelectCommand = cmd;
    using (DataTable dt = new DataTable())
    {
        sda.Fill(dt);
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt, "Customers");

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
}