修改测试

bklLiudl

2024-07-23 675b8bcc4a3630d95e3d0b97d933e63442075ecb

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
 
using Aspose.Cells;
//using NPOI.SS.UserModel;
using System;
using System.Data;
 
namespace Git.Storage.Common.Excel
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class AsposeExcel
    {
 
        private string outFileName = "";
        private string fullFilename = "";
        private Workbook book = null;
        private Worksheet sheet = null;
 
        private Action<Cell> action = (Cell cellItem) =>
            {
                cellItem.Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                cellItem.Style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
 
                cellItem.Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                cellItem.Style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
 
                cellItem.Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                cellItem.Style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
 
                cellItem.Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                cellItem.Style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
            };
 
        /// <summary>
        /// //导出构造数
        /// </summary>
        /// <param name="outfilename"></param>
        /// <param name="tempfilename"></param>
        public AsposeExcel(string outfilename, string tempfilename)
        {
            outFileName = outfilename;
            book = new Workbook();
            // book.Open(tempfilename);这里我们暂时不用模板
            sheet = book.Worksheets[0];
 
        }
        /// <summary>
        /// //导入构造数
        /// </summary>
        /// <param name="fullfilename"></param>
        public AsposeExcel(string fullfilename)
        {
            fullFilename = fullfilename;
        }
 
        private void AddTitle(string title, int columnCount)
        {
            sheet.Cells.Merge(0, 0, 2, columnCount);
            Cell cell1 = sheet.Cells[0, 0];
            cell1.PutValue(title);
            cell1.Style.HorizontalAlignment = TextAlignmentType.Center;
            //cell1.Style.Font.Color = System.Drawing.Color.Blue;
            cell1.Style.Font.Size = 20;
            cell1.Style.Font.IsBold = true;
 
        }
 
        private void AddHeader(DataTable dt)
        {
 
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                action(sheet.Cells[2, col]);
                sheet.Cells[2, col].PutValue(dt.Columns[col].ColumnName);
                sheet.Cells[2, col].Style.Font.IsBold = true;
                // sheet.Cells[2, col].Style.Font.Size = 13;
            }
        }
 
        private void AddBody(DataTable dt)
        {
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    action(sheet.Cells[r + 3, c]);
                    sheet.Cells[r + 3, c].PutValue(dt.Rows[r][c].ToString());
 
                }
            }
        }
        /// <summary>
        /// //导出------------
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public Boolean DatatableToExcel(DataTable dt, string sheetName, string title)
        {
            Boolean yn = false;
            try
            {
                sheet.Name = sheetName;
 
                AddTitle(title, dt.Columns.Count);
                AddHeader(dt);
                AddBody(dt);
                sheet.AutoFitColumns();
                book.Save(outFileName);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
            }
        }
        /// <summary>
        /// 导入
        /// </summary>
        /// <returns></returns>
        public DataTable ExcelToDatatalbe(string sheetName)
        {
            Workbook book = new Workbook();
            book.Open(fullFilename);
            Worksheet sheet = book.Worksheets[sheetName];
            Cells cells = sheet.Cells;
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
 
            return dt_Import;
        }
    }
}