using System; using System.Collections.Generic; using System.Linq; using System.Text; using org.in2bits.MyXls; using System.Data; using System.Text.RegularExpressions; using System.IO; using NPOI; using NPOI.HPSF; using NPOI.HSSF; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util; using NPOI.POIFS; using NPOI.Util; using System.Web; namespace Util { public class MyExcelUtil { /// <summary> /// DateTimeRowIndexes Starts From 1 /// </summary> /// <param name="FileName"></param> /// <param name="DateTimeRowIndexes">Starts From 1</param> /// <returns>DataTable</returns> public static DataTable ReadExcelXSL(string FileName, int[] DateRowIndex) { XlsDocument doc = new XlsDocument(FileName); Worksheet ws = doc.Workbook.Worksheets[0]; DataTable dt = new DataTable(); if (ws.Rows.Count > 1) { Row HeadRow = ws.Rows[1]; for (ushort i = 1; i <= HeadRow.CellCount; i++) dt.Columns.Add("C" + i); for (ushort i = 2; i < ws.Rows.Count; i++) { DataRow row = dt.NewRow(); Row dataRow = ws.Rows[i]; for (ushort j = 1; j <= dataRow.CellCount; j++) { object CellValue = dataRow.GetCell(j).Value; if (DateRowIndex != null && DateRowIndex.Length > 0 && DateRowIndex.Contains<int>(j)) { if (Regex.IsMatch(CellValue.ToString(), @"^\d{4}-\d{1,2}-\d{1,2}$")) { CellValue = CellValue.ToString(); } else { CellValue = Convert.ToDateTime("1900-1-1").AddDays(Convert.ToInt32(CellValue)).ToString("yyyy-MM-dd"); } } row["C" + j] = CellValue; } dt.Rows.Add(row); } } return dt; } /// <summary> /// 读取excel , 默认第一行为标头 /// </summary> /// <param name="strFileName"s>excel文档路径</param> /// <param name="DateTimeRowIndexes">Starts From 1</param> /// <returns>DataTable</returns> public static DataTable ReadExcel(string strFileName, int[] DateRowIndex) { try { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 1; j <= cellCount; j++) { HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add("C" + j); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); if (row == null || string.IsNullOrEmpty(Convert.ToString(row.GetCell(0)))) break; DataRow dataRow = dt.NewRow(); for (int j = 0; j < cellCount; j++) { object CellValue = row.GetCell(j); if (DateRowIndex != null && DateRowIndex.Length > 0 && DateRowIndex.Contains<int>(j + 1)) { if (CellValue == null || CellValue.ToString() == "") { CellValue = "1900-01-01"; } else { if (CellValue.ToString().Contains("/")) { CellValue = CellValue.ToString().Replace("/", "-"); } if (Regex.IsMatch(CellValue.ToString(), @"^\d{4}-\d{1,2}-\d{1,2}$")) { CellValue = CellValue.ToString(); } else { CellValue = Convert.ToDateTime("1900-1-1").AddDays(Convert.ToInt32(CellValue)).ToString("yyyy-MM-dd"); } } } dataRow[j] = CellValue; } dt.Rows.Add(dataRow); } return dt; } catch (Exception e) { throw e; } } public static void ToExcel(DataTable dt, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); //填充表头 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } //填充内容 for (int i = 0; i < dt.Rows.Count; i++) { dataRow = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } MemoryStream ms = new MemoryStream(); //传回客户端 workbook.Write(ms); workbook = null; ms.Flush(); ms.Position = 0; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(title) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");//导出到客户端 HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.End(); ms.Close();//释放 ms.Dispose(); } } }