Ask the instructor about how to export C # Excel without exporting hidden columns,

Ask the instructor about how to export C # Excel without exporting hidden columns,

First, create a GridView and place it in the able

 

To open the topic designer, perform operations on the data:

I performed the following hiding operation on the aa bb data lines:

In this way, aa bb is hidden in the GridView, but if no new data is operated, the exported Excel contains the aa bb columns. Therefore, it is very important to save New data. The Code throws:

1 private DataTable GetGridViewDT (GridView view) 2 {3 if (view. rowCount = 0) 4 return null; 5 DataTable dt = new DataTable (); 6 var cols = view. columns. where (p => p. visible ). orderBy (p => p. visibleIndex); 7 for (int I = 0; I <view. rowCount; I ++) 8 {9 List <object> values = new List <object> (); 10 foreach (var col in cols) 11 {12 object value = view. getRowCellValue (I, col); 13 if (I = 0) // Add column 14 {15 dt. columns. add (col. fieldName, col. columnType); 16} 17 values. add (value); 18} 19 dt. rows. add (values. toArray (); 20} 21 return dt; 22}

By the way, the ExcelHelper class is provided:

Using System; using System. collections. generic; using System. linq; using System. text; using NPOI. SS. userModel; using NPOI. XSSF. userModel; using NPOI. HSSF. userModel; using System. IO; using System. data; using System. componentModel; using System. windows. forms; using DevExpress. xtraGrid. views. grid; namespace RpnTest. excel {public class ExcelHelperTrue: IDisposable {private string fileName = null; // file name private I Workbook workbook = null; private FileStream fs = null; private bool disposed; public ExcelHelperTrue (string fileName) {this. fileName = fileName; disposed = false ;} /// <summary> /// import the able data to excel /// </summary> /// <param name = "data"> data to be imported </ param> /// <param name = "isColumnWritten"> whether to import the column name of the DataTable </param> /// <param name = "sheetName"> sheet of the excel file to be imported </param> /// <returns> the number of imported data rows (including the column name One row) </returns> public int DataTableToExcel (DataTable data, string sheetName, bool isColumnWritten) {int I = 0; int j = 0; int count = 0; ISheet sheet = null; fs = new FileStream (fileName, FileMode. openOrCreate, FileAccess. readWrite); if (fileName. indexOf (". xlsx ")> 0) // workbook version 2007 = new XSSFWorkbook (); else if (fileName. indexOf (". xls ")> 0) // workbook 2003 = new HSSFWorkbook (); try {if (Workbook! = Null) {sheet = workbook. createSheet (sheetName);} else {return-1;} if (isColumnWritten = true) // name of the column written to the DataTable {IRow row = sheet. createRow (0); for (j = 0; j <data. columns. count; ++ j) // traverses the {row. createCell (j ). setCellValue (data. columns [j]. columnName); // write data} count = 1;} else {count = 0;} for (I = 0; I <data. rows. count; ++ I) {IRow row = sheet. createRow (count); for (j = 0; j <data. Columns. count; ++ j) {CellType _ ype = GetExcelType (data. columns [j]. dataType. toString (); row. createCell (j ). setCellValue (data. rows [I] [j]. toString (); switch (_ datatype) {case CellType. numeric: row. createCell (j ). setCellValue (Convert. toDouble (data. rows [I] [j]); break; case CellType. string: row. createCell (j ). setCellValue (Convert. toString (data. rows [I] [j]); break; case CellType. formula: // IDataFo Rmat datastyle = workbook. createDataFormat (); var cell = row. createCell (j); cell. setCellValue (Convert. toDateTime (data. rows [I] [j]). toString ("yyyy-MM-dd"); // cell. cellStyle. dataFormat = datastyle. getFormat ("yyyy-MM-dd"); break; default: row. createCell (j ). setCellValue (data. rows [I] [j]. toString (); break;} // cell. setCellType (_ datatype); // if (_ datatype = CellType. formula) // cell. setCellFormula ("yyy Y-MM-dd ") ;}++ count ;}workbook. write (fs); // Write to excel return count;} catch (Exception ex) {Console. writeLine ("Exception:" + ex. message); return-1 ;}/// <summary> // Numeric = 0, // String = 1, // Formula = 2, // Blank = 3, // Boolean = 4, /// </summary> /// <param name = "type"> </param> /// <returns> </returns> // private CellType GetExcelType (string type) {CellType result = CellType. string; s Witch (type) {case "System. int32 ": case" System. decimal ": case" System. double ": result = CellType. numeric; break; case "System. dateTime ": result = CellType. formula; break;} return result ;} /// <summary> /// import the data in excel to the able /// </summary> /// <param name = "sheetName"> name of the sheet in the excel worksheet </param> /// <param name = "isFirstRowColumn"> whether the first row is the column name of the DataTable </param> /// <returns> returned DataTable </returns> Public DataTable exceltoable able (string sheetName, bool isFirstRowColumn) {ISheet sheet = null; DataTable data = new DataTable (); int startRow = 0; try {fs = new FileStream (fileName, FileMode. open, FileAccess. read); if (fileName. indexOf (". xlsx ")> 0) // workbook version 2007 = new XSSFWorkbook (fs); else if (fileName. indexOf (". xls ")> 0) // workbook 2003 = new HSSFWorkbook (fs); if (sheetName! = Null) {sheet = workbook. getSheet (sheetName); if (sheet = null) // if the sheet corresponding to the specified sheetName is not found, try to get the first sheet {sheet = workbook. getSheetAt (0) ;}} else {sheet = workbook. getSheetAt (0);} if (sheet! = Null) {IRow firstRow = sheet. getRow (0); int cellCount = firstRow. lastCellNum; // The number of the last cell in a row, that is, the total number of columns if (isFirstRowColumn) {for (int I = firstRow. firstCellNum; I <cellCount; ++ I) {ICell cell = firstRow. getCell (I); if (cell! = Null) {string cellValue = cell. StringCellValue; if (cellValue! = Null) {DataColumn column = new DataColumn (cellValue); data. columns. add (column) ;}} startRow = sheet. firstRowNum + 1;} else {startRow = sheet. firstRowNum;} // int rowCount = sheet in the last column. lastRowNum; for (int I = startRow; I <= rowCount; ++ I) {IRow row = sheet. getRow (I); if (row = null) continue; // The default value of a row without data is null DataRow dataRow = data. newRow (); for (int j = row. firstCellNum; j <cellCount; ++ J) {if (row. GetCell (j )! = Null) // Similarly, all cells without data are null dataRow [j] = row by default. getCell (j ). toString ();} data. rows. add (dataRow) ;}} return data ;}catch (Exception ex) {Console. writeLine ("Exception:" + ex. message); return null ;}} public void Dispose () {Dispose (true); GC. suppressFinalize (this);} protected virtual void Dispose (bool disposing) {if (! This. disposed) {if (disposing) {if (fs! = Null) fs. Close () ;}fs = null; disposed = true ;}}}}

Finally, you can click the Event code in the form button to call it:

Private void toexcelease click (object sender, EventArgs e) {DataTable dt = GetGridViewDT (gridView1); SaveFileDialog dlg = new SaveFileDialog () {Filter = "Excel file | *. xlsx "}; if (dlg. showDialog () = DialogResult. OK) {// string filename = DateTime. now. toString ("yyyymmddhhmmss"); string filename = dlg. fileName; using (ExcelHelperTrue helper = new ExcelHelperTrue (filename) {helper. dataTableToExcel (dt, "sheet", true);} Process. start (filename );}}

 

Thank you for resolving your problem and uploading it to the blog, so that more people who encounter the same problem as me can solve it as soon as possible!