private static void WriteDataTableToExcel(DataTable dt, string fileName) { var fileExt = fileName.Substring(fileName.LastIndexOf('.')); if (fileExt.ToUpper() == ".XLSX") { if (dt != null && dt.Rows.Count > 0) { var poDv = new ProcessOperator { BackgroundWork = () => WriteExcelByDataTable(dt, fileName) }; poDv.BackgroundWorkerCompleted += WriteExcel_Completed; poDv.Start(); } else { MessageBox.Show("当前表单没有任何数据"); } } else { MessageBox.Show("无法解析该后缀名的文件!"); } }
/// <summary> /// 读取Excel文件到DataSet /// </summary> /// <param name="filePath">Excel文件地址</param> /// <returns></returns> public DataSet ReturnDataSet(string filePath) { try { if (string.IsNullOrEmpty(filePath)) { return(ReturnDataSet()); } _filePath = filePath; _fileExt = filePath.Substring(filePath.LastIndexOf('.')); if (_fileExt.ToUpper() == ".XLSX" || _fileExt.ToUpper() == ".XLS") { var poDv = new ProcessOperator { BackgroundWork = ReadExcelToDataSet }; poDv.BackgroundWorkerCompleted += ReadExcel_Completed; poDv.Start(); } else { MessageBox.Show("无法解析该后缀名的文件!"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } return(_dsExcel); }
public static void Write(DataSet dsExcel, string fileName) { try { // if (string.IsNullOrEmpty(fileName)) // { // Write(dsExcel); // } // else // { if (dsExcel != null && dsExcel.Tables.Count > 0) { if (File.Exists(fileName)) { File.Delete(fileName); } //_saveFileDialog.FileName = fileName; //if (_saveFileDialog.ShowDialog() == DialogResult.OK) { //_writeSheetName = new List<string>(); foreach (DataTable dt in dsExcel.Tables) { if (dt == null || dt.TableName == null || dt.TableName == "") { continue; } ExcelReader.RemoveEmptyRow(dt); //string name = _saveFileDialog.FileName; var dt1 = dt; var poDv = new ProcessOperator { MessageInfo = @"正在写入" + Path.GetFileName(fileName) + "," + dt.TableName, BackgroundWork = () => WriteExcelByDataTable(dt1, fileName) }; poDv.Start(); } WriteExcel_Completed(null, null); } } else { MessageBox.Show("当前没有任何表单!"); } // } } catch (Exception ex) { MessageBox.Show("无法解析文件路径!\n" + ex); } }
/// <summary> /// 读取Excel文件到DataSet /// </summary> /// <param name="sheetNames">读取Excel文件工作表名称集合</param> /// <returns></returns> public DataSet ReturnDataSet(List <string> sheetNames) { var openFileDialog = new OpenFileDialog { Filter = "Execl files (*.xls,*.xlsx)|*.xls;*.xlsx" }; //openFileDialog.RestoreDirectory = true; //openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal); if (openFileDialog.ShowDialog() == DialogResult.OK) { _sheetNames = sheetNames; _filePath = openFileDialog.FileName; _fileExt = openFileDialog.FileName.Substring(openFileDialog.FileName.LastIndexOf('.')); var poDv = new ProcessOperator { BackgroundWork = ReadExcelToDataSet }; poDv.BackgroundWorkerCompleted += ReadExcel_Completed; poDv.Start(); } return(_dsExcel); }