222

schangxiang@126.com

2024-11-28 88079606346044bb3315a8165d8d6219c72240e7

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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
using System.Data;
 
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
 
namespace Admin.NET.Core.Helper.ExcelHelper
{
    public class Excel
    {
        private HSSFWorkbook _workBook;
        private ISheet _wbSheet = null;
        private DataColumnCollection _columns = null;
 
        private int _col = 0;    //total columns
        private int _row = 0;    //total rows
        //private int _sheet = 0;  //total sheets
        private int _sheetRowNum = 65536;  //each sheet allow rows
 
        public Excel()
        {
            InstanceWorkBook();
        }
 
        /// <summary>
        /// 实例方法
        /// </summary>
        /// <param name="columns">表头</param>
        public Excel(DataColumnCollection columns)
        {
            _columns = columns;
            InstanceWorkBook();
        }
 
        private void InstanceWorkBook()
        {
            _workBook = new HSSFWorkbook();
 
            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "BaiyiTimes";
            _workBook.DocumentSummaryInformation = dsi;
 
            var si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Etimes Secure Document System Log Backup";
            _workBook.SummaryInformation = si;
        }
 
        private DataColumnCollection GetColumns(DataColumnCollection columns)
        {
            return columns == null || columns.Count == 0 ? _columns : columns;
        }
 
        private ISheet GetSheet(ISheet sheet)
        {
            return sheet == null ? _wbSheet : sheet;
        }
 
        private void CreateHeader(ISheet sheet, DataColumnCollection columns)
        {
            _columns = GetColumns(columns);
 
            var oRow = sheet.CreateRow(0);
            oRow.Height = 20 * 20;
            foreach (DataColumn column in _columns)
            {
                var oCell = oRow.CreateCell(_col);
 
                var style1 = _workBook.CreateCellStyle();
                style1.Alignment = HorizontalAlignment.Center;
                style1.VerticalAlignment = VerticalAlignment.Center;
 
                var font = _workBook.CreateFont();
                font.IsBold = true;
                font.FontHeightInPoints = 11;
                style1.SetFont(font);
 
                oCell.CellStyle = style1;
                var name = column.ColumnName;
                oCell.SetCellValue(name.ToString());
                sheet.SetColumnWidth(_col, 60 * 60);
                _col++;
            }
            _row++;
        }
 
        public ISheet CreateSheet()
        {
            return CreateSheet(null, null);
        }
 
        public ISheet CreateSheet(DataColumnCollection columns, string name)
        {
            _wbSheet = _workBook.CreateSheet(name);
            CreateHeader(_wbSheet, columns);
            _col = 0;    //total columns
            _row = 1;
            return _wbSheet;
        }
 
        public void SetRowValue(DataRowCollection rows, ISheet sheet)
        {
            _wbSheet = GetSheet(sheet);
            foreach (DataRow row in rows)
            {
                SetRowValue(row);
            }
        }
 
        public void SetRowValue(DataRow row)
        {
            var style1 = _workBook.CreateCellStyle();
            style1.Alignment = HorizontalAlignment.Center;
            style1.VerticalAlignment = VerticalAlignment.Center;
            //if (_row % _sheetRowNum == 0)
            //{
            //    CreateSheet();
            //}
            var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);
 
            var obj = string.Empty;
            var cell = 0;
            foreach (DataColumn column in _columns)
            {
                obj = row[column.ColumnName].ToString();
                var cells = oRow.CreateCell(cell);
                cells.CellStyle = style1;
                cells.SetCellValue(obj);
                cell++;
            }
            _row++;
        }
 
        public void SaveAs(string filePath)
        {
            if (File.Exists(filePath)) File.Delete(filePath);
            var file = new FileStream(filePath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }
        public MemoryStream SaveAsMemoryStream()
        {
            var ms = new MemoryStream();
            _workBook.Write(ms);
            return ms;
        }
    }
}