using Ionic.Zip;
#region 提取归档清单
/// <summary>/// 提取归档清单
/// </summary>
public void ExtractZipByName(string sFilePath)
{
string sExtFilePath = "C:\\LJTemp\\测试.XLS";
if (sFilePath.Contains(CBE_JH.Text + "资料"))
{
using (ZipFile zip = ZipFile.Read(sFilePath, new ReadOptions() { Encoding = Encoding.Default }))
{
List<string> LResult = new List<string>();
var vResults = zip.EntryFileNames.ToArray();
for (int i = 0; i < vResults.Length; i++)
{
if (vResults[i].Last().ToString() != "/")
{
LResult.Add(vResults[i]);
}
}
string sXlsPath = CBE_JH.Text + "测试.XLS";
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
ZipEntry e = zip[sXlsPath];
e.Extract("C:\\LJTemp\\");
DataTable dt = ExcelHelperForCs.ImportFromExcel(sExtFilePath, 1).Tables[0];
dt.Columns.Remove("件内页数");
dt.Columns.Remove("份数");
dt.Columns.Remove("备注");
dt.Columns.Add("是否存在", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
List<string> LResult2 = LResult.Where(str => str.Contains(dt.Rows[i][2].ToString())).ToList();
dt.Rows[i][3] = LResult2.Count > 0 ? "存在" : "文件不存在于压缩包中";
}
GC_Main.DataSource = dt;
sw.Stop();
TimeSpan TS = sw.Elapsed;
string sAppCastTime = String.Format("归档清单读取完毕,共计耗时{0}ms.", TS.TotalMilliseconds);
LC_AddStatus.Text = sAppCastTime;
}
catch (Exception ex)
{
string sResult = string.Empty;
sResult = ex.Message;
if (sResult.Contains("already exists."))
{
MessageBox.Show("目录 C:\\LJTemp已存在名为 测试.XLS 的临时文件,请重新上传!", "错误提示");
}
else
{
XtraMessageBox.Show(ex.Message, "错误提示");
}
GC_Main.DataSource = null;
}
FileHelper.DeleteFolder("C:\\LJTemp");
}
}
else
{
MessageBox.Show("所选ZIP文件中不包含关键字: " 测试资料名称", "错误提示");
}
}
#endregion
public static class ExcelHelperForCs
{
/// <summary>
/// 判断是否为兼容模式
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static bool GetIsCompatible(string filePath)
{
return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
}
/// <summary>
/// 创建工作薄
/// </summary>
/// <param name="isCompatible"></param>
/// <returns></returns>
private static IWorkbook CreateWorkbook(bool isCompatible)
{
if (isCompatible)
{
return new HSSFWorkbook();
}
else
{
return new XSSFWorkbook();
}
}
/// <summary>
/// 创建工作薄(依据文件流)
/// </summary>
/// <param name="isCompatible"></param>
/// <param name="stream"></param>
/// <returns></returns>
private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
{
if (isCompatible)
{
return new HSSFWorkbook(stream);
}
else
{
return new XSSFWorkbook(stream);
}
}
/// <summary>
/// 创建表格头单元格
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
return style;
}
/// <summary>
/// 从工作表中生成DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列,则不再继续向后读取
cellCount = i + 1;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && row.GetCell(0) != null)
{
row.GetCell(0).SetCellType(CellType.String);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
if (!string.IsNullOrEmpty(row.Cells[1].StringCellValue) && !string.IsNullOrEmpty(row.Cells[2].StringCellValue))
{
table.Rows.Add(dataRow);
}
}
}
return table;
}
#region 公共导入方法
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataTable</returns>
public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
if (int.TryParse(sheetName, out sheetIndex))
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = GetIsCompatible(excelFilePath);
return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible);
}
}
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataSet</returns>
public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
{
DataSet ds = new DataSet();
IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = GetIsCompatible(excelFilePath);
return ImportFromExcel(stream, headerRowIndex, isCompatible);
}
}
#endregion
#region 公共转换方法
/// <summary>
/// 将Excel的列索引转换为列名,列索引从0开始,列名从A开始。如第0列为A,第1列为B...
/// </summary>
/// <param name="index">列索引</param>
/// <returns>列名,如第0列为A,第1列为B...</returns>
public static string ConvertColumnIndexToColumnName(int index)
{
index = index + 1;
int system = 26;
char[] digArray = new char[100];
int i = 0;
while (index > 0)
{
int mod = index % system;
if (mod == 0) mod = system;
digArray[i++] = (char)(mod - 1 + 'A');
index = (index - 1) / 26;
}
StringBuilder sb = new StringBuilder(i);
for (int j = i - 1; j >= 0; j--)
{
sb.Append(digArray[j]);
}
return sb.ToString();
}
/// <summary>
/// 转化日期
/// </summary>
/// <param name="date">日期</param>
/// <returns></returns>
public static DateTime ConvertDate(object date)
{
string dtStr = (date ?? "").ToString();
DateTime dt = new DateTime();
if (DateTime.TryParse(dtStr, out dt))
{
return dt;
}
try
{
string spStr = "";
if (dtStr.Contains("-"))
{
spStr = "-";
}
else if (dtStr.Contains("/"))
{
spStr = "/";
}
string[] time = dtStr.Split(spStr.ToCharArray());
int year = Convert.ToInt32(time[2]);
int month = Convert.ToInt32(time[0]);
int day = Convert.ToInt32(time[1]);
string years = Convert.ToString(year);
string months = Convert.ToString(month);
string days = Convert.ToString(day);
if (months.Length == 4)
{
dt = Convert.ToDateTime(date);
}
else
{
string rq = "";
if (years.Length == 1)
{
years = "0" + years;
}
if (months.Length == 1)
{
months = "0" + months;
}
if (days.Length == 1)
{
days = "0" + days;
}
rq = "20" + years + "-" + months + "-" + days;
dt = Convert.ToDateTime(rq);
}
}
catch
{
throw new Exception("日期格式不正确,转换日期失败!");
}
return dt;
}
/// <summary>
/// 转化数字
/// </summary>
/// <param name="d">数字字符串</param>
/// <returns></returns>
public static decimal ConvertDecimal(object d)
{
string dStr = (d ?? "").ToString();
decimal result = 0;
if (decimal.TryParse(dStr, out result))
{
return result;
}
else
{
throw new Exception("数字格式不正确,转换数字失败!");
}
}
#endregion
}