Home  >  Article  >  Backend Development  >  C# programming Excel import and export (source code download) (Part 2)

C# programming Excel import and export (source code download) (Part 2)

黄舟
黄舟Original
2017-02-06 16:55:571773browse

3. Excel导出

3.1 导出流程

C# programming Excel import and export (source code download) (Part 2)

3.2 NPOI操作代码

说明:把List转换为Excel

步骤:

①创建一个工作簿(Workbook);

②在工作簿上创建一个工作表(Sheet);

③在工作表上创建第一行(row),第一行为列头,依次写入cellHeard的值(做为列名)。

④循环遍历List集合,每循环一遍创建一个行(row),然后根据cellHeard的键(属性名称)依次从List中的实体对象取值存放到单元格内。

代码:

/// <summary>
/// 实体类集合导出到Excle2003
/// </summary>
/// <param name="cellHeard">单元头的Key和Value:{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
/// <param name="enList">数据源</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>文件的下载地址</returns>
public static string EntityListToExcel2003(Dictionary<string, string> cellHeard, IList enList, string sheetName)
{
    try
    {
        string fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
        string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址,供给前台下载
        string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
 
        // 1.检测是否存在文件夹,若不存在就建立个文件夹
        string directoryName = Path.GetDirectoryName(filePath);
        if (!Directory.Exists(directoryName))
        {
            Directory.CreateDirectory(directoryName);
        }
 
        // 2.解析单元格头部,设置单元头的中文名称
        HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿
        ISheet sheet = workbook.CreateSheet(sheetName); // 工作表
        IRow row = sheet.CreateRow(0);
        List<string> keys = cellHeard.Keys.ToList();
        for (int i = 0; i < keys.Count; i++)
        {
            row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值
        }
 
        // 3.List对象的值赋值到Excel的单元格里
        int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头)
        foreach (var en in enList)
        {
            IRow rowTmp = sheet.CreateRow(rowIndex);
            for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称,获取对象指定属性的值
            {
                string cellValue = ""; // 单元格的值
                object properotyValue = null; // 属性的值
                System.Reflection.PropertyInfo properotyInfo = null; // 属性的信息
 
                // 3.1 若属性头的名称包含&#39;.&#39;,就表示是子类里的属性,那么就要遍历子类,eg:UserEn.UserName
                if (keys[i].IndexOf(".") >= 0)
                {
                    // 3.1.1 解析子类属性(这里只解析1层子类,多层子类未处理)
                    string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                    string subClassName = properotyArray[0]; // &#39;.&#39;前面的为子类的名称
                    string subClassProperotyName = properotyArray[1]; // &#39;.&#39;后面的为子类的属性名称
                    System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
                    if (subClassInfo != null)
                    {
                        // 3.1.2 获取子类的实例
                        var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
                        // 3.1.3 根据属性名称获取子类里的属性类型
                        properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
                        if (properotyInfo != null)
                        {
                            properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值
                        }
                    }
                }
                else
                {
                    // 3.2 若不是子类的属性,直接根据属性名称获取对象对应的属性
                    properotyInfo = en.GetType().GetProperty(keys[i]);
                    if (properotyInfo != null)
                    {
                        properotyValue = properotyInfo.GetValue(en, null);
                    }
                }
 
                // 3.3 属性值经过转换赋值给单元格值
                if (properotyValue != null)
                {
                    cellValue = properotyValue.ToString();
                    // 3.3.1 对时间初始值赋值为空
                    if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
                    {
                        cellValue = "";
                    }
                }
 
                // 3.4 填充到Excel的单元格里
                rowTmp.CreateCell(i).SetCellValue(cellValue);
            }
            rowIndex++;
        }
 
        // 4.生成文件
        FileStream file = new FileStream(filePath, FileMode.Create);
        workbook.Write(file);
        file.Close();
 
        // 5.返回下载路径
        return urlPath;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

3.3 C#逻辑操作代码

说明:对Excel转换后的List进行后续操作;如:检测有效性、持久化存储等等

步骤:

①获取List集合。

②调用3.2,将List转换为Excel文件。

③服务器存储Excel文件并返回下载链接。

代码:

public void ExportExcel(HttpContext context)
{
    try
    {
        // 1.获取数据集合
        List<UserEntity> enlist = new List<UserEntity>() {
            new UserEntity{Name="刘一",Age=22,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=80,MathScores=90}},
            new UserEntity{Name="陈二",Age=23,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=81,MathScores=91} },
            new UserEntity{Name="张三",Age=24,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=82,MathScores=92} },
            new UserEntity{Name="李四",Age=25,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=83,MathScores=93} },
            new UserEntity{Name="王五",Age=26,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=84,MathScores=94} },
        };
 
        // 2.设置单元格抬头
        // key:实体对象属性名称,可通过反射获取值
        // value:Excel列的名称
        Dictionary<string, string> cellheader = new Dictionary<string, string> {
            { "Name", "姓名" },
            { "Age", "年龄" },
            { "GenderName", "性别" },
            { "TranscriptsEn.ChineseScores", "语文成绩" },
            { "TranscriptsEn.MathScores", "数学成绩" },
        };
 
        // 3.进行Excel转换操作,并返回转换的文件下载链接
        string urlPath = ExcelHelper.EntityListToExcel2003(cellheader, enlist, "学生成绩");
        System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();
        context.Response.ContentType = "text/plain";
        context.Response.Write(js.Serialize(urlPath)); // 返回Json格式的内容
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

3.4 代码分析

核心代码主要是cellheader与List之间的映射关系:

C# programming Excel import and export (source code download) (Part 2)

4. 源码下载

4.1 运行图

C# programming Excel import and export (source code download) (Part 2)

4.2 下载地址 

百度网盘: http://pan.baidu.com/s/1o69We8M

CSDN:http://download.csdn.net/download/polk6/8974195

The above is the content of Excel import and export (source code download) for C# programming (below). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn