当前位置:网站首页>[C.NET tool class] i. reading and writing in Excel

[C.NET tool class] i. reading and writing in Excel

2021-06-23 23:07:42 Black Cat Constable

Excel Reading and writing depend on third-party class libraries , There are more choices here 、 Also independent of installation Office Component's NPOI.

Support xls and xlsx Format , The distinguishing method is shown in the code .

stay Nuget Find and install on the project :

 

 

 

 

1.Excel Reading data :

  1   public partial class ExcelFile
  2     {
  3         /// <summary>
  4         ///  Read Excel file 
  5         /// </summary>
  6         /// <param name="filePath"> The path of the read file </param>
  7         /// <param name="hasTitle"> Whether the file contains column headers </param>
  8         /// <returns></returns>
  9         public static DataSet Read(string filePath,bool hasTitle = false)
 10         {
 11             IWorkbook wk = null;
 12             string extension = Path.GetExtension(filePath);
 13             try
 14             {
 15                 FileStream fs = File.OpenRead(filePath);
 16                 if (extension.Equals(".xls"))
 17                 {
 18                     // hold xls The data in the file is written wk in 
 19                     wk = new HSSFWorkbook(fs);
 20                 }
 21                 else
 22                 {
 23                     // hold xlsx The data in the file is written wk in 
 24                     wk = new XSSFWorkbook(fs);
 25                 }
 26 
 27                 fs.Close();
 28 
 29                 DataSet ds = new DataSet();
 30                 for (int tableindex = 0; tableindex < wk.NumberOfSheets; tableindex++)
 31                 {
 32                     // Read current table data 
 33                     ISheet sheet = wk.GetSheetAt(tableindex);
 34 
 35                     IRow row = sheet.GetRow(0);  // Read current row data 
 36                                                  //LastRowNum  Is the total number of rows in the current table -1( Be careful )
 37 
 38                     DataTable dt = new DataTable();
 39                     dt.TableName = sheet.SheetName;
 40 
 41                     for (int i = 0; i <= sheet.LastRowNum; i++)
 42                     {
 43                         row = sheet.GetRow(i);  // Read current row data 
 44                         if (row != null)
 45                         {
 46                             if (hasTitle && i ==0)
 47                             {
 48                                 //LastCellNum  Is the total number of columns in the current row 
 49                                 for (int j = 0; j < row.LastCellNum; j++)
 50                                 {
 51                                     // Read page... Of the line j Column data 
 52                                     string value = row.GetCell(j).ToString();
 53                                     DataColumn dataColumn = new DataColumn();
 54                                     dataColumn.ColumnName = value;
 55                                     dataColumn.DataType = typeof(string);
 56                                     dt.Columns.Add(dataColumn);
 57                                 }
 58                                 continue;
 59                             }
 60 
 61                             
 62                             if (dt.Columns.Count == 0 || dt.Columns.Count != row.LastCellNum)
 63                             {
 64                                 for (int j = 0; j < row.LastCellNum; j++)
 65                                 {
 66                                     DataColumn dataColumn = new DataColumn();
 67                                     dataColumn.ColumnName = "Column" + j;
 68                                     dataColumn.DataType = typeof(string);
 69                                     dt.Columns.Add(dataColumn);
 70                                 }
 71                             }
 72 
 73                             DataRow dataRow = dt.NewRow();
 74 
 75                             //LastCellNum  Is the total number of columns in the current row 
 76                             for (int j = 0; j < row.LastCellNum; j++)
 77                             {
 78                                 // Read page... Of the line j Column data 
 79                                 string value = row.GetCell(j).ToString();
 80                                 dataRow[j] = value;
 81                             }
 82 
 83                             dt.Rows.Add(dataRow);
 84                         }
 85                     }
 86                     ds.Tables.Add(dt);
 87                 }
 88 
 89                 if (ds.Tables.Count == 0)
 90                 {
 91                     return null;
 92                 }
 93                 else
 94                 {
 95                     return ds;
 96                 }
 97             }
 98             catch (Exception e)
 99             {
100                 throw e;
101             }
102         }
103     }

 

2.Excel Write data

  1 public partial class ExcelFile
  2     {
  3         /// <summary>
  4         ///  Read Excel file 
  5         /// </summary>
  6         /// <param name="filePath"> The path of the read file </param>
  7         /// <returns></returns>
  8         public static bool Write(DataTable dt,string filePath)
  9         {
 10             try
 11             {
 12                 // Create a workbook   
 13                 IWorkbook workbook = new HSSFWorkbook();
 14 
 15                 // Get file suffix 
 16                 string extension = Path.GetExtension(filePath);
 17 
 18                 // Create the corresponding class according to the specified file format 
 19                 if (extension.Equals(".xls"))
 20                 {
 21                     workbook = new HSSFWorkbook();
 22                 }
 23                 else
 24                 {
 25                     workbook = new XSSFWorkbook();
 26                 }
 27 
 28                 // Create a  sheet  surface 
 29                 ISheet sheet = workbook.CreateSheet();
 30 
 31                 // Create a line 
 32                 IRow rowH = sheet.CreateRow(0);
 33 
 34                 // Create a cell 
 35                 ICell cell = null;
 36 
 37                 // Create cell styles 
 38                 ICellStyle cellStyle = workbook.CreateCellStyle();
 39 
 40                 // Create format 
 41                 IDataFormat dataFormat = workbook.CreateDataFormat();
 42 
 43                 // Format as text , It can also be for text, namely dataFormat.GetFormat("text")
 44                 cellStyle.DataFormat = dataFormat.GetFormat("0");
 45 
 46                 // Set column name 
 47                 foreach (DataColumn col in dt.Columns)
 48                 {
 49                     // Create cells and set cell contents 
 50                     rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
 51 
 52                     // Format cell 
 53                     rowH.Cells[col.Ordinal].CellStyle = cellStyle;
 54                 }
 55 
 56                 // Write data 
 57                 for (int i = 0; i < dt.Rows.Count; i++)
 58                 {
 59                     // Skip the first line , The first act is to list 
 60                     IRow row = sheet.CreateRow(i + 1);
 61                     for (int j = 0; j < dt.Columns.Count; j++)
 62                     {
 63                         cell = row.CreateCell(j);
 64                         cell.SetCellValue(dt.Rows[i][j].ToString());
 65                         cell.CellStyle = cell.CellStyle;
 66                     }
 67                 }
 68 
 69                 // Set the path and name of the new file 
 70                 if (File.Exists(filePath))
 71                 {
 72                     File.Delete(filePath);
 73                 }
 74 
 75                 // create a file 
 76                 //FileStream file = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write);
 77 
 78                 FileStream fs = File.OpenWrite(filePath);
 79                 workbook.Write(fs);// To the open one Excel Write the form in the file and save it .  
 80                 fs.Close();
 81 
 82                 return true;
 83             }
 84             catch (Exception ex)
 85             {
 86                 throw ex;
 87             }
 88 
 89 
 90             #region  Old method 
 91 
 92             //// Create a workbook   
 93             //IWorkbook wb = new HSSFWorkbook();
 94             //string extension = Path.GetExtension(filePath);
 95             //// Create the corresponding class according to the specified file format 
 96             //if (extension.Equals(".xls"))
 97             //{
 98             //    wb = new HSSFWorkbook();
 99             //}
100             //else
101             //{
102             //    wb = new XSSFWorkbook();
103             //}
104 
105             //ICellStyle style1 = wb.CreateCellStyle();// style 
106             //style1.Alignment = HorizontalAlignment.Left;// Horizontal alignment of text 
107             //style1.VerticalAlignment = VerticalAlignment.Center;// Text vertical alignment 
108             //                                                    // Set borders 
109             //style1.BorderBottom = BorderStyle.Thin;
110             //style1.BorderLeft = BorderStyle.Thin;
111             //style1.BorderRight = BorderStyle.Thin;
112             //style1.BorderTop = BorderStyle.Thin;
113             //style1.WrapText = true;// Word wrap 
114 
115             //ICellStyle style2 = wb.CreateCellStyle();// style 
116             //IFont font1 = wb.CreateFont();// typeface 
117             //font1.FontName = " Regular script ";
118             //font1.Color = HSSFColor.Red.Index;// The font color 
119             //font1.Boldweight = (short)FontBoldWeight.Normal;// Bold style 
120             //style2.SetFont(font1);// The font in the style sets the specific font style 
121             //                      // Set background color 
122             //style2.FillForegroundColor = HSSFColor.Yellow.Index;
123             //style2.FillPattern = FillPattern.SolidForeground;
124             //style2.FillBackgroundColor = HSSFColor.Yellow.Index;
125             //style2.Alignment = HorizontalAlignment.Left;// Horizontal alignment of text 
126             //style2.VerticalAlignment = VerticalAlignment.Center;// Text vertical alignment 
127 
128             //ICellStyle dateStyle = wb.CreateCellStyle();// style 
129             //dateStyle.Alignment = HorizontalAlignment.Left;// Horizontal alignment of text 
130             //dateStyle.VerticalAlignment = VerticalAlignment.Center;// Text vertical alignment 
131             //                                                       // Set the data display format 
132             //IDataFormat dataFormatCustom = wb.CreateDataFormat();
133             //dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
134 
135             //// Create a form 
136             //ISheet sheet = wb.CreateSheet("Sheet0");
137             //// Set column width 
138             //int[] columnWidth = { 10, 10, 20, 10 };
139             //for (int i = 0; i < columnWidth.Length; i++)
140             //{
141             //    // Set column width ,256* Number of characters , Because the unit is 1/256 Characters 
142             //    sheet.SetColumnWidth(i, 256 * columnWidth[i]);
143             //}
144 
145             //// Test data 
146             //int rowCount = 3, columnCount = 4;
147             //object[,] data = {
148             //    {" Column 0", " Column 1", " Column 2", " Column 3"},
149             //    {"", 400, 5.2, 6.01},
150             //    {"", true, "2014-07-02", DateTime.Now}
151             //    // Date can be directly transferred to string ,NPOI Will automatically identify 
152             //    // If it is DateTime type , Then set CellStyle.DataFormat, Otherwise it will be displayed as a number 
153             //};
154 
155             //IRow row;
156             //ICell cell;
157 
158             //for (int i = 0; i < rowCount; i++)
159             //{
160             //    row = sheet.CreateRow(i);// Create the i That's ok 
161             //    for (int j = 0; j < columnCount; j++)
162             //    {
163             //        cell = row.CreateCell(j);// Create the j Column 
164             //        cell.CellStyle = j % 2 == 0 ? style1 : style2;
165             //        // Set different types of cell
166             //        object obj = data[i, j];
167             //        SetCellValue(cell, data[i, j]);
168             //        // If it's a date , Set the date display format 
169             //        if (obj.GetType() == typeof(DateTime))
170             //        {
171             //            cell.CellStyle = dateStyle;
172             //        }
173             //        // If you want to automatically adjust the column width according to the content , It needs to be done first setCellValue Call again 
174             //        //sheet.AutoSizeColumn(j);
175             //    }
176             //}
177 
178             //// merge cell , If all the cells to be merged have data , Only the top left corner will be kept 
179             ////CellRangeAddress(0, 2, 0, 0), Merge 0-2 That's ok ,0-0 Cell of column 
180             //CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
181             //sheet.AddMergedRegion(region);
182 
183             //try
184             //{
185             //    FileStream fs = File.OpenWrite(filePath);
186             //    wb.Write(fs);// To the open one Excel Write the form in the file and save it .  
187             //    fs.Close();
188             //}
189             //catch (Exception e)
190             //{
191             //    Debug.WriteLine(e.Message);
192             //}
193 
194             #endregion
195         }
196     }

 

版权声明
本文为[Black Cat Constable]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/06/20210623230647969S.html

随机推荐