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);
}
