将数据保存在 csv 文件中

Question

我在 .csv 文件中保存数据时遇到问题。

     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();
    }

问题出在文件中，我在 A 列中有数据。我想将 DataRow 格式的一行拆分为 8 个部分，分别放入 8 个不同的列中。我的函数正常工作，因为它看不到选项卡（“\t”）。

我无法发布图像，所以我尝试在 csv 文件中描述结果：

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

这是我的示例行，我想将其粉碎为 8 列：

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

“#\t#”没有帮助。结果是：

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

有一些表格，但我的观点是没有空间，但过渡到下一个单元格:(

“\u0008”也没有帮助。

Answer 1

首先，您说您正在写入 CSV（逗号分隔值）文件。
但是，您实际上正在写入一个制表符分隔的文件。
并且，您需要在行之间写 /r/n：

这有效：

    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();

Answer 2

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 = 
quot;{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(
quot;Created worksheet for TableName: '{dt.TableName}'");
            else
                _log.Error(
quot;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;
}

Answer 3

我倾向于同意@Hossein。我认为这是一个编码问题。我运行了你的代码，就像在我的机器上一样，它运行得很好。

Answer 4

以防万一您自从一年前写下这个问题以来仍然没有找到解决方案：

您可以使用 KBCSV 库，它非常流行并且可以处理几乎您需要的所有内容。它默认使用 csv，但可以轻松修改以处理 tsv。

对于一个非常简单且容易破坏的解决方案，您可以尝试：
string.Join("\t", rzad) + "\r\n"

这不会考虑包含制表符的字段。如果字段包含制表符，则此方法将无用。通常，包含分隔符的字段是双引号的，而包含双引号的字段是双双引号的。

实现这一点并不太困难，但它会重新发明轮子，因为 Kent Boogart 已经花了很多时间考虑一些我不会意识到的边缘情况。

Answer 5

以下函数用于写入 csv 文件。

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

然后通过另一个简单的多行示例调用此函数

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);

，每行由换行符“\n”分隔。

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

Answer 6

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());
                        }
                    }

Answer 7

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();
            }
        }
    }
}