c# - 如何在 excel csv 文件中添加两个表？

Question

我正在使用以下代码创建 excel CSV 文件，它对我生成第一个图像表工作正常。但我想为第二个图像生成 CSV。任何人都可以告诉我如何在单个文件中添加两个表以及如何在没有列标题的情况下插入记录以及如何在 excel csv 中添加空行和空列？

CsvExport myExport = new CsvExport();
string fileName = "AbsentEmployeesReport" + Session["id"] + ".csv";
foreach (var item in lstEmployees)
{
myExport.AddRow();
myExport["Enroll ID"] = item.EnrollNumber;
myExport["Employee (Firt and Last)"] = item.Name;
myExport["Department"] = item.Department;
myExport["Absent Count"] = item.AbsentCount;
}
string myCsv = myExport.Export();
myExport.ExportToFile(Server.MapPath("/Reports/" + fileName), myCsv);

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Collections.Generic;

/// <summary>
/// Simple CSV export
public class CsvExport
{
  /// <summary>
  /// To keep the ordered list of column names
  /// </summary>
  List<string> fields = new List<string>();

  /// <summary>
  /// The list of rows
  /// </summary>
  List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();

  /// <summary>
  /// The current row
  /// </summary>
  Dictionary<string, object> currentRow { get { return rows[rows.Count - 1]; } }

  /// <summary>
  /// Set a value on this column
  /// </summary>
  public object this[string field]
  {
    set
    {
      // Keep track of the field names, because the dictionary loses the ordering
      if (!fields.Contains(field)) fields.Add(field);
      currentRow[field] = value;
    }
  }

  /// <summary>
  /// Call this before setting any fields on a row
  /// </summary>
  public void AddRow()
  {
    rows.Add(new Dictionary<string, object>());
  }

  /// <summary>
  /// Converts a value to how it should output in a csv file
  /// If it has a comma, it needs surrounding with double quotes
  /// Eg Sydney, Australia -> "Sydney, Australia"
  /// Also if it contains any double quotes ("), then they need to be replaced with quad quotes[sic] ("")
  /// Eg "Dangerous Dan" McGrew -> """Dangerous Dan"" McGrew"
  /// </summary>
  string MakeValueCsvFriendly(object value)
  {
    if (value == null) return "";
    if (value is INullable && ((INullable)value).IsNull) return "";
    if (value is DateTime)
    {
      if (((DateTime)value).TimeOfDay.TotalSeconds==0)
        return ((DateTime)value).ToString("yyyy-MM-dd");
      return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
    }
    string output = value.ToString();
    if (output.Contains(",") || output.Contains("\""))
      output = '"' + output.Replace("\"", "\"\"") + '"';
    return output;
  }

  /// <summary>
  /// Output all rows as a CSV returning a string
  /// </summary>
  public string Export()
  {
    StringBuilder sb = new StringBuilder();

    // The header
    foreach (string field in fields)
      sb.Append(field).Append(",");
    sb.AppendLine();

    // The rows
    foreach (Dictionary<string, object> row in rows)
    {
      foreach (string field in fields)
        sb.Append(MakeValueCsvFriendly(row[field])).Append(",");
      sb.AppendLine();
    }

    return sb.ToString();
  }

  /// <summary>
  /// Exports to a file
  /// </summary>
  public void ExportToFile(string path)
  {
    File.WriteAllText(path, Export());
  }

  /// <summary>
  /// Exports as raw UTF8 bytes
  /// </summary>
  public byte[] ExportToBytes()
  {
    return Encoding.UTF8.GetBytes(Export());
  }
}

如何生成如下所示的 Excel CSV:

Answer 1

嗯，CSV 代表逗号分隔值，如果您通过记事本打开生成的文件，您将真正了解这些数据的实际情况，并且您将了解您的要求。

1. 要插入空行，只需执行“”、“”、“”、“”
2. 要插入空列(假设第一列为空)，您可以执行“”、“data1”、“data2”
3. 要插入新表 2，您需要执行与创建表 1 相同的操作，但首先在表 1 之后插入表头。所以数据应该是这样的:

第 1-1 列、第 1-2 列、第 1-3 列
datat1-1,"data1-2,data11-3
第2-1列、第2-2列、第2-3列
数据12-1,数据12-2,数据12-3"
......