public static bool Collect(string inExcel, string outExcel) { bool b = false; var inStream = new FileStream(inExcel, FileMode.Open); IWorkbook inbook = null; if (inExcel.EndsWith("xls")) { inbook = new HSSFWorkbook(inStream); outExcel += "x"; } else if (inExcel.EndsWith(".xlsx")) { inbook = new XSSFWorkbook(inStream); } //template var templateStream = new FileStream("protected.xlsx", FileMode.OpenOrCreate); var outBook = new XSSFWorkbook(); XSSFSheet outSheet = (outBook.GetSheet("jp") ?? outBook.CreateSheet("jp")) as XSSFSheet;//new XSSFSheet();// templateStream.Close(); int totalCount = 0; //var outSheet = outBook.Sheet("jp");//new XSSFSheet();// //outBook.Add(outSheet as XSSFSheet); var hrow = outSheet.GetRow(0); if (hrow == null) { outSheet.Cell(0, 0).SetCellValue("jp"); outSheet.Cell(0, 1).SetCellValue("trans"); outSheet.Cell(0, 2).SetCellValue("trans_jd"); outSheet.Cell(0, 3).SetCellValue("i"); outSheet.Cell(0, 4).SetCellValue("j"); outSheet.Cell(0, 5).SetCellValue("SheetName"); hrow = outSheet.GetRow(0); } var head = new ExcelHead(hrow); var locked = outBook.CreateCellStyle(); locked.IsLocked = true; locked.WrapText = true; locked.ShrinkToFit = true; var nolocked = outBook.CreateCellStyle(); nolocked.IsLocked = false; nolocked.WrapText = true; nolocked.ShrinkToFit = true; outSheet.SetDefaultColumnStyle(head[HeadIdx.jp], locked); outSheet.SetDefaultColumnStyle(head[HeadIdx.trans], nolocked); outSheet.SetDefaultColumnStyle(head[HeadIdx.trans_jd], nolocked); outSheet.SetDefaultColumnStyle(head[HeadIdx.i], locked); outSheet.SetDefaultColumnStyle(head[HeadIdx.j], locked); outSheet.SetDefaultColumnStyle(head[HeadIdx.SheetName], locked); int count = 0; int MaxRowNum = 10000; int MaxCellNum = 256; foreach (var sheet in inbook.AllSheets()) { for (int i = 0; i <= sheet.LastRowNum && i < MaxRowNum; ++i) { var row = sheet.Row(i); for (int j = 0; j < row.LastCellNum && j < MaxCellNum; ++j) { var v = row.Cell(j).SValue(); var matches = Regex.Matches(v, regular + "+.*"); if (matches.Count > 0) { ++count; ++totalCount; var c = outSheet.Cell(count, head[HeadIdx.jp]); c.SetCellValue(v); //c.CellStyle.IsLocked = cellLock; c = outSheet.Cell(count, head[HeadIdx.trans]); c.SetCellValue("译文"); //c.CellStyle.IsLocked = false; c = outSheet.Cell(count, head[HeadIdx.trans_jd]); c.SetCellValue("校对"); //c.CellStyle.IsLocked = false; c = outSheet.Cell(count, head[HeadIdx.i]); c.SetCellValue(i); //c.CellStyle.IsLocked = cellLock; c = outSheet.Cell(count, head[HeadIdx.j]); c.SetCellValue(j); //c.CellStyle.IsLocked = cellLock; c = outSheet.Cell(count, head[HeadIdx.SheetName]); c.SetCellValue(sheet.SheetName); //c.CellStyle.IsLocked = cellLock; } } } // outSheet.AutoSizeColumn(0, false, 64); //if(count == 0) //{ // var b = outBook.Remove(outSheet); // outBook.FirstVisibleTab = 0; // if(b == false) // { // Console.WriteLine("remove failed."); // } //} } //var pro = outSheet.AddProtection("654123"); inStream.Close(); if (totalCount > 0) { ++colCount; b = true; var infoSheet = outBook.Sheet("info");// infoSheet.Cell(0, 0).SetCellValue(inExcel); var outDir = Path.GetDirectoryName(outExcel); if (!Directory.Exists(outDir)) { Directory.CreateDirectory(outDir); } var outStream = new FileStream(outExcel, FileMode.Create); outStream.Position = 0; // no use //outBook.LockRevision(); //outBook.LockStructure(); //outBook.LockWindows(); outBook.Write(outStream); outStream.Close(); Console.WriteLine(colCount + " >>> " + outExcel); } return(b); }