HelloGit/CreateExcel.cs at master · YoshiroTamura/HelloGit · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
    public class QueryFields : TransferFields
    {
        public int QueryID { get; set; }
        public int QueryFieldNo { get; set; }
        public string TableName { get; set; }
        public int ExportExcel { get; set; }

        //public string DatabaseName { get; set; }
    }

       public static bool CreateExcel<T>(string filePath, List<T> targetList, List<QueryFields> qfldList)
        {
            var res = false;

            try
            {
                IWorkbook workbook;
                workbook = new XSSFWorkbook(); //ブック作成
                ISheet sheet = workbook.CreateSheet("Sheet1"); //シート作成

                var obj = Activator.CreateInstance<T>();
                var objprops = obj.GetType().GetProperties();

                var rNo = 0;
                var cNo = 0;

                //項目行
                foreach (var fld in qfldList)
                {
                    fld.PropNo = GetPropNo(objprops, fld.FieldName);
                    var row = sheet.GetRow(rNo) ?? sheet.CreateRow(rNo);
                    var cell = row.GetCell(cNo) ?? row.CreateCell(cNo);
                    cell.SetCellValue(fld.Description);
                    cNo++;
                }
                rNo++;

                foreach (var data in targetList)
                {

                    cNo = 0;

                    var props = data.GetType().GetProperties();
                    foreach (var fld in qfldList)
                    {
                        if (fld.PropNo >= 0)
                        {
                            var type = props[fld.PropNo].PropertyType;
                            var row = sheet.GetRow(rNo) ?? sheet.CreateRow(rNo);
                            var cell = row.GetCell(cNo) ?? row.CreateCell(cNo);
                            object value = props[fld.PropNo].GetValue(data);

                            if (type == typeof(string) && value != null && !string.IsNullOrEmpty(((string)value).Trim()))
                            {
                                cell.SetCellValue(((string)value).Trim());
                            }
                            else if (type == typeof(decimal) || type == typeof(int) || type == typeof(double) || type == typeof(long))
                            {
                                cell.SetCellValue(Convert.ToDouble(value));
                            }
                            else if (type == typeof(DateTime))
                            {
                                cell.SetCellValue((DateTime)value);
                                var style = workbook.CreateCellStyle();
                                style.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/mm/dd");
                                cell.CellStyle = style;
                            }
                            cNo++;
                        }
                    }

                    //foreach (PropertyInfo prop in data.GetType().GetProperties())
                    //{
                    //    var type = prop.PropertyType;
                    //    var row = sheet.GetRow(rNo) ?? sheet.CreateRow(rNo);
                    //    var cell = row.GetCell(cNo) ?? row.CreateCell(cNo);
                    //    object value = prop.GetValue(data);

                    //    //Debug.WriteLine(prop.Name + "  " +  type.Name + ">>>" + value);


                    //    if (type == typeof(string) && value != null && !string.IsNullOrEmpty(((string)value).Trim()))
                    //    {
                    //        cell.SetCellValue(((string)value).Trim());
                    //    }
                    //    else if (type == typeof(decimal) || type == typeof(int) || type == typeof(double))
                    //    {
                    //        cell.SetCellValue(Convert.ToDouble(value));
                    //    }
                    //    else if (type == typeof(DateTime))
                    //    {
                    //        cell.SetCellValue((DateTime)value);
                    //        var style = book.CreateCellStyle();
                    //        style.DataFormat = book.CreateDataFormat().GetFormat("yyyy/mm/dd");
                    //        cell.CellStyle = style;
                    //    }
                    //    cNo++;
                    //}
                    rNo++;
                }

                //ブックを保存
                using (var fs = new FileStream(filePath, FileMode.Create))
                {
                    workbook.Write(fs);
                }

                //if (workbook != null)
                //{
                //    workbook.Close();
                //    Debug.WriteLine("*****************close*******************");

                //}
                res = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            return res;

        }