/// <summary>
/// 获取目录下文件路径
/// </summary>
/// <param name="path"></param>
/// <param name="type">例如:"zip","xls"</param>
/// <returns></returns>
List<string> GetFilesPath(string path,string type)
{
List<string> filesPath = new List<string>();
FileInfo[] files = GetFileInfo(path);
foreach (FileInfo item in files)
{
if (item.FullName.Contains(type))
{
filesPath.Add(item.FullName);
}
}
return filesPath;
}
/// <summary>
/// 获取路径下的所有文件
/// </summary>
/// <param name="path">文件夹路径</param>
/// <returns>所有文件</returns>
FileInfo[] GetFileInfo(string path)
{
DirectoryInfo root = new DirectoryInfo(path);
FileInfo[] files = root.GetFiles();
return files;
}
读取.xls文件到DataSet
/// <summary>
/// 读取Excel表
/// </summary>
/// <param name="filePath">路径</param>
/// <param name="table">表名</param>
/// <returns>DataSet</returns>
public static DataSet LoadDataFromExcel(string filePath, string table)
{
try
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [" + table + "$]";//可是更改Sheet名称，比如sheet2，等等 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, table);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
//MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
//MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
导出DataTable到.xls格式，如果是.xlsx将代码中HSS改为XSS
首先引入NPOI
/// <summary>
/// DataTable导出到Excel文件 .xls格式
/// </summary>
/// <param name="dtSource">源DataSet</param>
/// <param name="strFileName">保存位置</param>
public static void DataTableToExcel(DataTable dt, string strFileName)
{
using (MemoryStream ms = DataSetToExcel(dt))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, , data.Length);
fs.Flush();
}
}
} /// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
public static MemoryStream DataSetToExcel(DataTable dt)
{
HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(dt.TableName.ToString()); #region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI"; // workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "文件作者信息"; //填加xls文件作者信息
//si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
//si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
//si.Comments = "作者信息"; //填加xls文件作者信息
//si.Title = "标题信息"; //填加xls文件标题信息
//si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
// workbook.SummaryInformation = si;
}
#endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽
/*
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
*/
int rowIndex = ;
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == )
{
//if (rowIndex != 0)
//{
// sheet = (XSSFSheet)workbook.CreateSheet();
//}
#region 表头及样式
//{
//XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
//headerRow.HeightInPoints = 25;
//headerRow.CreateCell(0).SetCellValue(strHeaderText); //HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//// headStyle.Alignment = CellHorizontalAlignment.CENTER;
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 20;
//font.Boldweight = 700;
//headStyle.SetFont(font);
//headerRow.GetCell(0).CellStyle = headStyle;
//// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
////headerRow.Dispose();
//}
#endregion #region 列头及样式
//{
// XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
// XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// //headStyle.Alignment = CellHorizontalAlignment.CENTER;
// XSSFFont font = (XSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 10;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// foreach (DataColumn column in ds.Tables[k].Columns)
// {
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// headerRow.GetCell(column.Ordinal).CellStyle = headStyle; // //设置列宽
// // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
// }
// // headerRow.Dispose();
//}
#endregion rowIndex = ;
}
#endregion #region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
} }
#endregion
rowIndex++;
} using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
return ms;
}
}
导出DataTable到.csv格式
public static void Export2CSV(DataTable data, string filename, bool exportHeader = true)
{
if (File.Exists(filename))
File.Delete(filename); var buffer = new StringBuilder();
if (exportHeader)
{
for (var i = ; i < data.Columns.Count; i++)
{
buffer.AppendFormat("\"{0}\"", data.Columns[i].ColumnName);
if (i < data.Columns.Count - )
buffer.Append(",");
}
buffer.AppendLine();
} for (var i = ; i < data.Rows.Count; i++)
{
for (var j = ; j < data.Columns.Count; j++)
{
buffer.AppendFormat("\"{0}\"", data.Rows[i][j].ToString());
if (j < data.Columns.Count - )
buffer.Append(",");
}
buffer.AppendLine();
} File.WriteAllText(filename, buffer.ToString(), Encoding.Default);
}
Zip解压 引入插件：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Checksums; namespace Jerry.API.C
{
/// <summary>
/// 适用与ZIP压缩
/// </summary>
public class ZipHelper
{
#region 压缩 /// <summary>
/// 递归压缩文件夹的内部方法
/// </summary>
/// <param name="folderToZip">要压缩的文件夹路径</param>
/// <param name="zipStream">压缩输出流</param>
/// <param name="parentFolderName">此文件夹的上级文件夹</param>
/// <returns></returns>
private static bool ZipDirectory(string folderToZip, ZipOutputStream zipStream, string parentFolderName)
{
bool result = true;
string[] folders, files;
ZipEntry ent = null;
FileStream fs = null;
Crc32 crc = new Crc32(); try
{
ent = new ZipEntry(Path.Combine(parentFolderName, Path.GetFileName(folderToZip) + "/"));
zipStream.PutNextEntry(ent);
zipStream.Flush(); files = Directory.GetFiles(folderToZip);
foreach (string file in files)
{
fs = File.OpenRead(file); byte[] buffer = new byte[fs.Length];
fs.Read(buffer, , buffer.Length);
ent = new ZipEntry(Path.Combine(parentFolderName, Path.GetFileName(folderToZip) + "/" + Path.GetFileName(file)));
ent.DateTime = DateTime.Now;
ent.Size = fs.Length; fs.Close(); crc.Reset();
crc.Update(buffer); ent.Crc = crc.Value;
zipStream.PutNextEntry(ent);
zipStream.Write(buffer, , buffer.Length);
} }
catch
{
result = false;
}
finally
{
if (fs != null)
{
fs.Close();
fs.Dispose();
}
if (ent != null)
{
ent = null;
}
GC.Collect();
GC.Collect();
} folders = Directory.GetDirectories(folderToZip);
foreach (string folder in folders)
if (!ZipDirectory(folder, zipStream, folderToZip))
return false; return result;
} /// <summary>
/// 压缩文件夹
/// </summary>
/// <param name="folderToZip">要压缩的文件夹路径</param>
/// <param name="zipedFile">压缩文件完整路径</param>
/// <param name="password">密码</param>
/// <returns>是否压缩成功</returns>
public static bool ZipDirectory(string folderToZip, string zipedFile, string password)
{
bool result = false;
if (!Directory.Exists(folderToZip))
return result; ZipOutputStream zipStream = new ZipOutputStream(File.Create(zipedFile));
zipStream.SetLevel();
if (!string.IsNullOrEmpty(password)) zipStream.Password = password; result = ZipDirectory(folderToZip, zipStream, ""); zipStream.Finish();
zipStream.Close(); return result;
} /// <summary>
/// 压缩文件夹
/// </summary>
/// <param name="folderToZip">要压缩的文件夹路径</param>
/// <param name="zipedFile">压缩文件完整路径</param>
/// <returns>是否压缩成功</returns>
public static bool ZipDirectory(string folderToZip, string zipedFile)
{
bool result = ZipDirectory(folderToZip, zipedFile, null);
return result;
} /// <summary>
/// 压缩文件
/// </summary>
/// <param name="fileToZip">要压缩的文件全名</param>
/// <param name="zipedFile">压缩后的文件名</param>
/// <param name="password">密码</param>
/// <returns>压缩结果</returns>
public static bool ZipFile(string fileToZip, string zipedFile, string password)
{
bool result = true;
ZipOutputStream zipStream = null;
FileStream fs = null;
ZipEntry ent = null; if (!File.Exists(fileToZip))
return false; try
{
fs = File.OpenRead(fileToZip);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, , buffer.Length);
fs.Close(); fs = File.Create(zipedFile);
zipStream = new ZipOutputStream(fs);
if (!string.IsNullOrEmpty(password)) zipStream.Password = password;
ent = new ZipEntry(Path.GetFileName(fileToZip));
zipStream.PutNextEntry(ent);
zipStream.SetLevel(); zipStream.Write(buffer, , buffer.Length); }
catch
{
result = false;
}
finally
{
if (zipStream != null)
{
zipStream.Finish();
zipStream.Close();
}
if (ent != null)
{
ent = null;
}
if (fs != null)
{
fs.Close();
fs.Dispose();
}
}
GC.Collect();
GC.Collect(); return result;
} /// <summary>
/// 压缩文件
/// </summary>
/// <param name="fileToZip">要压缩的文件全名</param>
/// <param name="zipedFile">压缩后的文件名</param>
/// <returns>压缩结果</returns>
public static bool ZipFile(string fileToZip, string zipedFile)
{
bool result = ZipFile(fileToZip, zipedFile, null);
return result;
} /// <summary>
/// 压缩文件或文件夹
/// </summary>
/// <param name="fileToZip">要压缩的路径</param>
/// <param name="zipedFile">压缩后的文件名</param>
/// <param name="password">密码</param>
/// <returns>压缩结果</returns>
public static bool Zip(string fileToZip, string zipedFile, string password)
{
bool result = false;
if (Directory.Exists(fileToZip))
result = ZipDirectory(fileToZip, zipedFile, password);
else if (File.Exists(fileToZip))
result = ZipFile(fileToZip, zipedFile, password); return result;
} /// <summary>
/// 压缩文件或文件夹
/// </summary>
/// <param name="fileToZip">要压缩的路径</param>
/// <param name="zipedFile">压缩后的文件名</param>
/// <returns>压缩结果</returns>
public static bool Zip(string fileToZip, string zipedFile)
{
bool result = Zip(fileToZip, zipedFile, null);
return result; } #endregion #region 解压 /// <summary>
/// 解压功能(解压压缩文件到指定目录)
/// </summary>
/// <param name="fileToUnZip">待解压的文件</param>
/// <param name="zipedFolder">指定解压目标目录</param>
/// <param name="password">密码</param>
/// <returns>解压结果</returns>
public static bool UnZip(string fileToUnZip, string zipedFolder, string password)
{
bool result = true;
FileStream fs = null;
ZipInputStream zipStream = null;
ZipEntry ent = null;
string fileName; if (!File.Exists(fileToUnZip))
return false; if (!Directory.Exists(zipedFolder))
Directory.CreateDirectory(zipedFolder); try
{
zipStream = new ZipInputStream(File.OpenRead(fileToUnZip));
if (!string.IsNullOrEmpty(password)) zipStream.Password = password;
while ((ent = zipStream.GetNextEntry()) != null)
{
if (!string.IsNullOrEmpty(ent.Name))
{
fileName = Path.Combine(zipedFolder, ent.Name);
fileName = fileName.Replace('/', '\\');//change by Mr.HopeGi if (fileName.EndsWith("\\"))
{
Directory.CreateDirectory(fileName);
continue;
} fs = File.Create(fileName);
int size = ;
byte[] data = new byte[size];
while (true)
{
size = zipStream.Read(data, , data.Length);
if (size > )
fs.Write(data, , data.Length);
else
break;
}
}
}
}
catch
{
result = false;
}
finally
{
if (fs != null)
{
fs.Close();
fs.Dispose();
}
if (zipStream != null)
{
zipStream.Close();
zipStream.Dispose();
}
if (ent != null)
{
ent = null;
}
GC.Collect();
GC.Collect();
}
return result;
} /// <summary>
/// 解压功能(解压压缩文件到指定目录)
/// </summary>
/// <param name="fileToUnZip">待解压的文件</param>
/// <param name="zipedFolder">指定解压目标目录</param>
/// <returns>解压结果</returns>
public static bool UnZip(string fileToUnZip, string zipedFolder)
{
bool result = UnZip(fileToUnZip, zipedFolder, null);
return result;
} #endregion
}
}