僅供參考
參考網址:
https://learn.microsoft.com/en-us/office/open-xml/spreadsheet/how-to-create-a-spreadsheet-document-by-providing-a-file-name
https://learn.microsoft.com/en-us/office/open-xml/getting-started
https://www.nuget.org/packages/DocumentFormat.OpenXml
https://blog.darkthread.net/blog/csvhelper/
https://www.cnblogs.com/geovindu/p/19161493
程式:
```
DataTable dataTable = ReadCsv(inputPath, encoding, delimiter);
ConvertDataTableToXls(dataTable, outputPath);
public static DataTable ReadCsv(string filePath, Encoding encoding, char delimiter)
{
try
{
using (var reader = new StreamReader(filePath, encoding))
using (var csv = new CsvReader(reader, new CsvHelper.Configuration.CsvConfiguration(CultureInfo.InvariantCulture)
{
Delimiter = delimiter.ToString(),
HasHeaderRecord = true,
IgnoreBlankLines = true,
TrimOptions = CsvHelper.Configuration.TrimOptions.Trim
}))
{
using (var dr = new CsvDataReader(csv))
{
var dt = new DataTable();
dt.Load(dr);
return dt;
}
}
}
catch (Exception ex)
{
throw new Exception($"Failed to read CSV file: {ex.Message}", ex);
}
}
public static void ConvertDataTableToXls(DataTable dataTable, string outputPath)
{
try
{
// Create a new spreadsheet document
using (SpreadsheetDocument document = SpreadsheetDocument.Create(outputPath, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
// Get the SheetData object
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
// Add header row
Row headerRow = new Row();
foreach (DataColumn column in dataTable.Columns)
{
Cell cell = CreateCell(column.ColumnName, CellValues.String);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
// Add data rows
foreach (DataRow row in dataTable.Rows)
{
Row dataRow = new Row();
foreach (var item in row.ItemArray)
{
CellValues cellType = GetCellValueType(item);
string cellValue = GetCellValueAsString(item, cellType);
Cell cell = CreateCell(cellValue, cellType);
dataRow.AppendChild(cell);
}
sheetData.AppendChild(dataRow);
}
// Save the workbook
workbookPart.Workbook.Save();
}
}
catch (Exception ex)
{
throw new Exception($"Failed to create XLS file: {ex.Message}", ex);
}
}
private static Cell CreateCell(string value, CellValues cellType)
{
Cell cell = new Cell();
cell.DataType = new EnumValue<CellValues>(cellType);
cell.CellValue = new CellValue(value);
return cell;
}
private static CellValues GetCellValueType(object value)
{
if (value == DBNull.Value)
return CellValues.String;
Type type = value.GetType();
if (type == typeof(int) || type == typeof(long) || type == typeof(short) || type == typeof(byte))
return CellValues.Number;
else if (type == typeof(float) || type == typeof(double) || type == typeof(decimal))
return CellValues.Number;
else if (type == typeof(DateTime))
return CellValues.Date;
else if (type == typeof(bool))
return CellValues.Boolean;
else
return CellValues.String;
}
private static string GetCellValueAsString(object value, CellValues cellType)
{
if (value == DBNull.Value)
return string.Empty;
switch (cellType)
{
case CellValues.Boolean:
return (bool)value ? "1" : "0";
case CellValues.Date:
DateTime dateValue = (DateTime)value;
// Excel stores dates as OLE Automation dates
return dateValue.ToOADate().ToString(CultureInfo.InvariantCulture);
case CellValues.Number:
return Convert.ToString(value, CultureInfo.InvariantCulture);
default:
return Convert.ToString(value);
}
}
```
