public List <ExcelItem> ReadExcel(string filePath, string sheetName = null) { List <ExcelItem> listExcelItems = new List <ExcelItem>(); try { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; IWorkbook workbook = null; bool isFirstRowColumn = true; var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); if (filePath.IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fs); } else if (filePath.IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fs); } if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 for (int i = 1 /*跳过第一行*/; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } var bianhaoCell = row.GetCell(1); if (null == bianhaoCell) { continue; } var bianhao = bianhaoCell.ToString(); if (string.IsNullOrEmpty(bianhao)) { continue; } var recordTimeCell = row.GetCell(2); if (null == recordTimeCell) { continue; } if (default(DateTime) == recordTimeCell.DateCellValue) { continue; } var recordTime = recordTimeCell.DateCellValue;//.ToString(); //var cc = new DateTime(); //if (string.IsNullOrEmpty(recordTime)) //{ // continue; //} //else //{ // var date = DateTime.Parse(recordTime); // recordTime = date.ToString("yyyy-MM-dd HH:mm:ss"); // cc = date; //} var excelItem = new ExcelItem(); excelItem.Bianhao = bianhao; excelItem.RecordDate = recordTime; //excelItem.RecordDate = cc; listExcelItems.Add(excelItem); } }//end if (sheet != null) } catch (Exception ex) { MessageBox.Show("" + ex); } return(listExcelItems); // MessageBox.Show("Done OK,file at D:\\"); }
public void ExportExcel(string tableName, List <Record> listRecords) { try { //创建一个工作簿 IWorkbook workbook = new XSSFWorkbook(); //创建一个 sheet 表 ISheet sheet = workbook.CreateSheet(tableName); //创建一行 IRow rowH = sheet.CreateRow(0); //创建一个单元格 ICell cell = null; //创建单元格样式 ICellStyle cellStyle = workbook.CreateCellStyle(); //创建格式 IDataFormat dataFormat = workbook.CreateDataFormat(); //设置为文本格式,也可以为 text,即 dataFormat.GetFormat("text"); cellStyle.DataFormat = dataFormat.GetFormat("@"); ICellStyle cellDateStyle = workbook.CreateCellStyle(); IDataFormat dateFormat = workbook.CreateDataFormat(); cellDateStyle.DataFormat = dateFormat.GetFormat("yyyy-mm-dd hh:mm:ss;@"); //设置列名 //foreach (DataColumn col in dt.Columns) //{ // //创建单元格并设置单元格内容 // rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption); // //设置单元格格式 // rowH.Cells[col.Ordinal].CellStyle = cellStyle; //} //设置第一行列名 // (DataColumn col in dt.Columns) { int start = 0; //创建单元格并设置单元格内容 rowH.CreateCell(start).SetCellValue("姓名"); //设置单元格格式 rowH.Cells[start].CellStyle = cellStyle; rowH.CreateCell(start + 1).SetCellValue("工号"); //设置单元格格式 rowH.Cells[start + 1].CellStyle = cellStyle; rowH.CreateCell(start + 2).SetCellValue("刷卡记录"); //设置单元格格式 rowH.Cells[start + 2].CellStyle = cellDateStyle; rowH.CreateCell(start + 3).SetCellValue("备注"); //设置单元格格式 rowH.Cells[start + 3].CellStyle = cellStyle; } //{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{{ int lastDay = -1; //var OndayDic = new Dictionary<string, int>{ // {"1003920",0 }, // {"1004984",0 }, // {"1004699",0 }, // {"1006056",0 } // }; //avoid hard code var OndayDic = new Dictionary <string, int>(); //init state foreach (var userBianhao in Constant.NameDictionary.Keys) { OndayDic.Add(userBianhao, 0); } List <ExcelItem> listExcelItems = new List <ExcelItem>(); for (int i = 0; i < listRecords.Count; i++) { var record = listRecords[i]; var excelItem = new ExcelItem(); excelItem.Bianhao = record.Bianhao; excelItem.Name = Constant.NameDictionary[record.Bianhao]; //excelItem.RecordDate = record.OnDate.ToString(); excelItem.RecordDate = record.OnDate; if (isLateOrEarlyOffWork(record.OnDate)) { excelItem.Note = "迟到或早退"; } if (-1 == lastDay) { lastDay = record.OnDate.Day; } else if (lastDay != record.OnDate.Day) { //统计当前缺勤 foreach (var item in OndayDic) { if (item.Value == 0) { ExcelItem absent = new ExcelItem(); absent.Bianhao = item.Key; absent.Name = Constant.NameDictionary[item.Key]; absent.Note = "缺席或请假"; listExcelItems.Add(absent); } else if (item.Value < 2) { ExcelItem absent = new ExcelItem(); absent.Bianhao = item.Key; absent.Name = Constant.NameDictionary[item.Key]; absent.Note = "漏打卡一次"; listExcelItems.Add(absent); } } //隔天,插入空记录 ExcelItem tmp = new ExcelItem(); listExcelItems.Add(tmp); //更新天 lastDay = record.OnDate.Day; //新的一天设置初始状态 var keys = new List <String>(); foreach (var itemkey in OndayDic.Keys) { keys.Add(itemkey); } foreach (var itemkey in keys) { OndayDic[itemkey] = 0; } } else {//同一天,非第一次 } OndayDic[record.Bianhao]++; listExcelItems.Add(excelItem); } //最后一天记录统计 //统计当前缺勤 foreach (var item in OndayDic) { if (item.Value == 0) { ExcelItem absent = new ExcelItem(); absent.Bianhao = item.Key; absent.Name = Constant.NameDictionary[item.Key]; absent.Note = "缺席或请假"; listExcelItems.Add(absent); } else if (item.Value < 2) { ExcelItem absent = new ExcelItem(); absent.Bianhao = item.Key; absent.Name = Constant.NameDictionary[item.Key]; absent.Note = "漏打卡一次"; listExcelItems.Add(absent); } } //}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}} //写入数据 for (int i = 0; i < listExcelItems.Count; i++) { //跳过第一行,第一行为列名 IRow row = sheet.CreateRow(i + 1); var excelItem = listExcelItems[i]; //for (int j = 0; j < 4; j++) { int j = 0; cell = row.CreateCell(j); cell.SetCellValue(excelItem.Name); cell = row.CreateCell(j + 1); cell.SetCellValue(excelItem.Bianhao); if (default(DateTime) == excelItem.RecordDate) { cell = row.CreateCell(j + 2); string dt = null; cell.SetCellValue(dt); cell.CellStyle = cellStyle; } else { cell = row.CreateCell(j + 2, CellType.Numeric); cell.SetCellValue(excelItem.RecordDate); cell.CellStyle = cellDateStyle; } cell = row.CreateCell(j + 3); cell.SetCellValue(excelItem.Note); if (string.IsNullOrEmpty(excelItem.Note)) { //cell.CellStyle = cellStyle; //cellStyle.DataFormat = dataFormat.GetFormat("@"); } } } //设置导出文件路径 string path = "D:\\" + tableName + "-智能部件苏州分部"; //设置新建文件路径及名称 string savePath = path + ".xlsx"; //创建文件 FileStream file = new FileStream(savePath, FileMode.CreateNew, System.IO.FileAccess.Write); //创建一个 IO 流 MemoryStream ms = new MemoryStream(); //写入到流 workbook.Write(ms); //转换为字节数组 byte[] bytes = ms.ToArray(); file.Write(bytes, 0, bytes.Length); file.Flush(); //还可以调用下面的方法,把流输出到浏览器下载 //OutputClient(bytes); //释放资源 bytes = null; ms.Close(); ms.Dispose(); file.Close(); file.Dispose(); workbook.Close(); sheet = null; workbook = null; MessageBox.Show("Done OK,file at D:\\"); } catch (Exception ex) { MessageBox.Show("" + ex); } }