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);
}
