Dataset 转 XLSX

Dataset 转 XLSX

 

public string DataSetToXlsx(DataSet vContent, string vOutputFilePath)
{
    string result;
    try
    {
        if (vContent != null && vContent.Tables.Count > 0)
        {
            using (SLDocument sldocument = new SLDocument())
            {
                if (File.Exists(vOutputFilePath))
                {
                    File.Delete(vOutputFilePath);
                }
                foreach (object obj in vContent.Tables)
                {
                    DataTable dataTable = (DataTable)obj;
                    sldocument.AddWorksheet(dataTable.TableName);
                    sldocument.ImportDataTable(1, 1, dataTable, true);
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        SLStyle slstyle = sldocument.CreateStyle();
                        if (dataTable.Columns[i].DataType.FullName.Equals("System.String"))
                        {
                            slstyle.FormatCode = "@";
                        }
                        else if (dataTable.Columns[i].DataType.FullName.Equals("System.DateTime"))
                        {
                            slstyle.FormatCode = "yyyy/mm/dd hh:mm:ss";
                        }
                        else if (dataTable.Columns[i].DataType.FullName.Equals("System.Int16"))
                        {
                            slstyle.FormatCode = "#";
                        }
                        else if (dataTable.Columns[i].DataType.FullName.Equals("System.Int32"))
                        {
                            slstyle.FormatCode = "#";
                        }
                        else if (dataTable.Columns[i].DataType.FullName.Equals("System.Int64"))
                        {
                            slstyle.FormatCode = "#";
                        }
                        else
                        {
                            slstyle.FormatCode = "General";
                        }
                        sldocument.SetColumnStyle(i + 1, slstyle);
                        sldocument.AutoFitColumn(i + 1, dataTable.Columns.Count, 300.0);
                    }
                    SLTable sltable = sldocument.CreateTable(1, 1, dataTable.Rows.Count + 1, dataTable.Columns.Count);
                    sltable.SetTableStyle(SLTableStyleTypeValues.Medium1);
                    sldocument.InsertTable(sltable);
                }
                sldocument.DeleteWorksheet("Sheet1");
                sldocument.SaveAs(vOutputFilePath);
            }
            result = "OK";
        }
        else
        {
            result = "DataSet is null";
        }
    }
    catch (Exception ex)
    {
        throw new Exception("DataSet to Xlsx : \n" + ex.Message);
    }
    return result;
}