pip install Spire.XLS

from spire.xls import *
from spire.xls.common import *

from spire.xls import *
from spire.xls.common import *

inputFile = "銷售數(shù)據(jù).csv"
outputFile = "銷售數(shù)據(jù).xlsx" 

# 創(chuàng)建工作簿對象
workbook = Workbook()

# 加載 CSV 文件
# 參數(shù)說明：文件路徑、分隔符、起始行、起始列
workbook.LoadFromFile(inputFile, ",", 1, 1)

# 獲取第一個工作表
sheet = workbook.Worksheets[0]

# 自動調整列寬以適應內容
sheet.AllocatedRange.AutoFitColumns()

# 保存為 Excel 2013 格式
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *

# 分號分隔的 CSV 文件（常見于歐洲）
inputFile_semicolon = "歐洲數(shù)據(jù).csv"
workbook1 = Workbook()
workbook1.LoadFromFile(inputFile_semicolon, ";", 1, 1)
workbook1.SaveToFile("歐洲數(shù)據(jù).xlsx", ExcelVersion.Version2013)
workbook1.Dispose()

# 制表符分隔的文件（TSV 格式）
inputFile_tab = "數(shù)據(jù)導出.tsv"
workbook2 = Workbook()
workbook2.LoadFromFile(inputFile_tab, "\t", 1, 1)
workbook2.SaveToFile("數(shù)據(jù)導出.xlsx", ExcelVersion.Version2013)
workbook2.Dispose()

# 豎線分隔的文件
inputFile_pipe = "系統(tǒng)日志.csv"
workbook3 = Workbook()
workbook3.LoadFromFile(inputFile_pipe, "|", 1, 1)
workbook3.SaveToFile("系統(tǒng)日志.xlsx", ExcelVersion.Version2013)
workbook3.Dispose()

from spire.xls import *
from spire.xls.common import *

inputFile = "員工信息.csv"
outputFile = "格式化員工信息.xlsx"

workbook = Workbook()
workbook.LoadFromFile(inputFile, ",", 1, 1)
sheet = workbook.Worksheets[0]

# 獲取數(shù)據(jù)范圍
dataRange = sheet.AllocatedRange

# 自動調整列寬
dataRange.AutoFitColumns()

# 格式化標題行（第一行）
headerRow = sheet.Rows[0]
headerRow.Style.Font.IsBold = True
headerRow.Style.Font.Color = Color.get_White()
headerRow.Style.KnownColor = Colors.DarkBlue

# 為整個數(shù)據(jù)區(qū)域添加邊框
border = sheet.Range["A1"].Style.Borders.GetBorder(BordersLineType.EdgeTop)
border.Color = Color.get_Black()
border.LineStyle = LineStyleType.Thin

border = sheet.Range["A1"].Style.Borders.GetBorder(BordersLineType.EdgeBottom)
border.Color = Color.get_Black()
border.LineStyle = LineStyleType.Thin

border = sheet.Range["A1"].Style.Borders.GetBorder(BordersLineType.EdgeLeft)
border.Color = Color.get_Black()
border.LineStyle = LineStyleType.Thin

border = sheet.Range["A1"].Style.Borders.GetBorder(BordersLineType.EdgeRight)
border.Color = Color.get_Black()
border.LineStyle = LineStyleType.Thin

# 設置交替行顏色（斑馬紋效果）
for i in range(1, sheet.LastRow + 1):
    row = sheet.Rows[i]
    if i % 2 == 0:
        row.Style.KnownColor = Colors.LightGray
    else:
        row.Style.KnownColor = Colors.White

# 保存文件
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

from spire.xls import *
from spire.xls.common import *

inputFile = "財務數(shù)據(jù).csv"
outputFile = "格式化財務數(shù)據(jù).xlsx"

workbook = Workbook()
workbook.LoadFromFile(inputFile, ",", 1, 1)
sheet = workbook.Worksheets[0]

# 忽略數(shù)字被識別為文本的警告
sheet.Range["B2:D100"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText

# 設置金額列為貨幣格式（假設 B 列是金額）
sheet.Columns[1].Style.NumberFormat = "￥#,##0.00"

# 設置百分比列（假設 C 列是百分比）
sheet.Columns[2].Style.NumberFormat = "0.00%"

# 設置日期列格式（假設 D 列是日期）
sheet.Columns[3].Style.NumberFormat = "yyyy-mm-dd"

# 自動調整列寬
sheet.AllocatedRange.AutoFitColumns()

# 保存文件
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

import os
from spire.xls import *
from spire.xls.common import *

class CSVToExcelConverter:
    def __init__(self):
        pass
    
    def convert_single(self, csv_file, excel_file, apply_formatting=True):
        """轉換單個 CSV 文件為 Excel"""
        
        workbook = Workbook()
        
        # 嘗試自動檢測分隔符
        delimiter = self._detect_delimiter(csv_file)
        
        # 加載 CSV 文件
        workbook.LoadFromFile(csv_file, delimiter, 1, 1)
        sheet = workbook.Worksheets[0]
        
        # 自動調整列寬
        sheet.AllocatedRange.AutoFitColumns()
        
        # 可選：應用格式化
        if apply_formatting:
            self._apply_basic_formatting(sheet)
        
        # 保存 Excel 文件
        workbook.SaveToFile(excel_file, ExcelVersion.Version2013)
        workbook.Dispose()
        
        print("已轉換：{0} -> {1}".format(
            os.path.basename(csv_file), 
            os.path.basename(excel_file)
        ))
    
    def _detect_delimiter(self, file_path):
        """自動檢測 CSV 文件的分隔符"""
        with open(file_path, 'r', encoding='utf-8') as f:
            first_line = f.readline()
        
        # 統(tǒng)計常見分隔符的出現(xiàn)次數(shù)
        delimiters = [',', ';', '\t', '|']
        counts = {d: first_line.count(d) for d in delimiters}
        
        # 返回出現(xiàn)次數(shù)最多的分隔符
        return max(counts, key=counts.get)
    
    def _apply_basic_formatting(self, sheet):
        """應用基本格式化樣式"""
        
        # 格式化標題行
        if sheet.LastRow >= 0:
            headerRow = sheet.Rows[0]
            headerRow.Style.Font.IsBold = True
            headerRow.Style.Font.Color = Color.get_White()
            headerRow.Style.KnownColor = Colors.DarkBlue
        
        # 設置交替行顏色
        for i in range(1, sheet.LastRow + 1):
            row = sheet.Rows[i]
            if i % 2 == 0:
                row.Style.KnownColor = Colors.LightGray
            else:
                row.Style.KnownColor = Colors.White
    
    def batch_convert(self, input_folder, output_folder, apply_formatting=True):
        """批量轉換文件夾中的所有 CSV 文件"""
        
        if not os.path.exists(output_folder):
            os.makedirs(output_folder)
        
        csv_files = [f for f in os.listdir(input_folder) 
                    if f.lower().endswith('.csv')]
        
        for filename in csv_files:
            csv_path = os.path.join(input_folder, filename)
            excel_name = os.path.splitext(filename)[0] + '.xlsx'
            excel_path = os.path.join(output_folder, excel_name)
            
            self.convert_single(csv_path, excel_path, apply_formatting)
        
        print("批量轉換完成！共處理 {0} 個 CSV 文件".format(len(csv_files)))
    
    def merge_multiple_csv(self, csv_files, output_excel):
        """合并多個 CSV 文件到一個 Excel 的不同工作表"""
        
        workbook = Workbook()
        workbook.Worksheets.Clear()
        
        for i, csv_file in enumerate(csv_files):
            if i == 0:
                # 第一個文件加載到默認工作表
                workbook.LoadFromFile(csv_file, ",", 1, 1)
                sheet = workbook.Worksheets[0]
                sheet.Name = os.path.splitext(os.path.basename(csv_file))[0]
            else:
                # 后續(xù)文件添加到新工作表
                temp_workbook = Workbook()
                temp_workbook.LoadFromFile(csv_file, ",", 1, 1)
                temp_sheet = temp_workbook.Worksheets[0]
                
                new_sheet = workbook.Worksheets.Add(
                    os.path.splitext(os.path.basename(csv_file))[0]
                )
                
                # 復制數(shù)據(jù)
                temp_range = temp_sheet.AllocatedRange
                temp_range.Copy(new_sheet.Range["A1"])
                
                temp_workbook.Dispose()
        
        # 統(tǒng)一調整列寬
        for sheet in workbook.Worksheets:
            sheet.AllocatedRange.AutoFitColumns()
        
        workbook.SaveToFile(output_excel, ExcelVersion.Version2013)
        workbook.Dispose()
        
        print("已合并 {0} 個 CSV 文件到：{1}".format(
            len(csv_files), 
            os.path.basename(output_excel)
        ))

# 使用示例
converter = CSVToExcelConverter()

# 單個文件轉換
converter.convert_single("銷售數(shù)據(jù).csv", "銷售數(shù)據(jù).xlsx")

# 批量轉換
converter.batch_convert("csv 數(shù)據(jù)文件夾", "excel 輸出文件夾")

# 合并多個 CSV 到一個 Excel
csv_list = ["一月數(shù)據(jù).csv", "二月數(shù)據(jù).csv", "三月數(shù)據(jù).csv"]
converter.merge_multiple_csv(csv_list, "季度匯總.xlsx")

# 如果默認加載出現(xiàn)亂碼，可以嘗試指定編碼
workbook.LoadFromFile(inputFile, ",", 1, 1)
# 或者先讀取文件內容再處理

sheet.Range["A1:D100"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText

sheet.AllocatedRange.AutoFitColumns()
# 或手動設置
sheet.Columns[0].ColumnWidth = 15

# 處理大型 CSV 時，可以逐行讀取并寫入
# 而不是一次性加載整個文件