public static void Replace(string inExcel)
{
try
{
Console.WriteLine(inExcel);
var inStream = new FileStream(inExcel, FileMode.Open);
IWorkbook inbook = null;
if (inExcel.EndsWith("xls"))
{
inbook = new HSSFWorkbook(inStream);
}
else if (inExcel.EndsWith(".xlsx"))
{
inbook = new XSSFWorkbook(inStream);
}
inStream.Close();
inStream.Dispose();
var infoSheet = inbook.Sheet("info");
string originExcel = infoSheet.Cell(0, 0).StringCellValue;
originExcel = originExcel.Replace("D:/a3", ProjRoot);
var outStream = new FileStream(originExcel, FileMode.Open);
if (outStream == null)
{
Console.WriteLine("{0} open failed.", originExcel);
throw new Exception(originExcel);
}
IWorkbook outbook = null;
if (originExcel.EndsWith(".xls"))
{
outbook = new HSSFWorkbook(outStream);
}
else if (originExcel.EndsWith(".xlsx"))
{
outbook = new XSSFWorkbook(outStream);
}
outStream.Close();
var jpSheet = inbook.Sheet("jp");
var hrow = jpSheet.GetRow(0);
var head = new ExcelHead(hrow);
for (var i = 1; i <= jpSheet.LastRowNum; ++i)
{
var row = jpSheet.Row(i);
var os = row.Cell(head[HeadIdx.jp]).StringCellValue;
var ts = row.Cell(head[HeadIdx.trans]).StringCellValue;
var x = (int)row.Cell(head[HeadIdx.i]).NumericCellValue;
var y = (int)row.Cell(head[HeadIdx.j]).NumericCellValue;
var sname = row.Cell(head[HeadIdx.SheetName]).StringCellValue;
var osheet = outbook.Sheet(sname);
var ocell = osheet.Cell(x, y);
if (ocell.StringCellValue == os)
{
ocell.SetCellValue(ts);
Debug.WriteLine(string.Format("\t[{4},{2},{3}]:[{0}] => [{1}]", os.Replace("\n", "\\n"), ts.Replace("\n", "\\n"), x, y, sname));
}
else
{
Debug.WriteLine(string.Format("\t{0}[{1}] <=> [{2}] not match", i, os.Replace("\n", "\\n"), ocell.StringCellValue.Replace("\n", "\\n")));
}
}
//File.Delete(originExcel);
outStream = new FileStream(originExcel, FileMode.Create);
outbook.Write(outStream);
outStream.Flush();
outStream.Close();
outStream.Dispose();
++colCount;
Console.WriteLine(colCount + " done: " + originExcel);
}
catch (Exception e)
{
Console.WriteLine(inExcel + ": " + e.ToString());
}
}
public void TranslateBtn(string outRoot = null)
{
if (string.IsNullOrWhiteSpace(outRoot))
{
outRoot = Instance().TranslatedDir;
}
try
{
var transExcelPath = outRoot + "/" + Name.Substring(Name.LastIndexOf('/') + 1) + ".xlsx";
var transStream = new FileStream(transExcelPath, FileMode.Open);
var transBook = new XSSFWorkbook(transStream);
transStream.Dispose();
var infoSheet = transBook.Sheet("info") as XSSFSheet;
var transSheet = transBook.Sheet("jp") as XSSFSheet;
var hrow = transSheet.GetRow(0);
var head = new ExcelHead(hrow);
int inFileIdx = 0;
foreach (var path in Pathes)
{
++inFileIdx;
EditorUtility.DisplayCancelableProgressBar(
"Translate ..."
, path.Replace("Assets/Application/Resource/ExcelData/", "")
, (float)(inFileIdx) / Pathes.Count());
var inbook = ExcelUtils.Open(path);
var rows = transSheet.Select(r => r.Cell(head[HeadIdx.FilePath]).SValueOneline() == path);
foreach (var trrow in rows)
{
var trans = trrow.Cell(head[HeadIdx.trans]).SValueOneline();
var jp = trrow.Cell(head[HeadIdx.jp]).SValueOneline();
if (jp[0] == '$')
{
AppLog.d(Tag, "使用引用: " + jp);
var row = transSheet.Row(int.Parse(jp.Substring(1)));
jp = row.Cell(head[HeadIdx.jp]).SValueOneline();
trans = row.Cell(head[HeadIdx.trans]).SValueOneline();
}
var sheetName = trrow.Cell(head[HeadIdx.SheetName]).SValueOneline();
var sheet = inbook.Sheet(sheetName);
var i = (int)trrow.Cell(head[HeadIdx.i]).NumericCellValue;
var j = (int)trrow.Cell(head[HeadIdx.j]).NumericCellValue;
var cell = sheet.Cell(i, j);
var ojp = cell.SValueOneline();
if (ojp == jp)
{
cell.SetCellValue(trans
.Replace("\\n", "\n")
.Replace("\\t", "\t")
.Replace("\\\"", "\""));
}
else
{
AppLog.e(Tag, i, j, ojp, " != ", jp, trans);
}
}
inbook.Write(path);
} // path
AppLog.d(Tag, "Translate: " + transExcelPath);
}
finally
{
EditorUtility.ClearProgressBar();
}
}
public void CollectJpBtn(string outRoot = null)
{
if (string.IsNullOrWhiteSpace(outRoot))
{
outRoot = Instance().OutJPDir;
}
try
{
var outExcelPath = outRoot + "/" + Name.Substring(Name.LastIndexOf('/') + 1) + ".xlsx";
var outTemplatePath = outRoot + "/template.xlsx";
// 从模板创建输出文件
var outBook = ExcelUtils.Open(outTemplatePath);
var outSheet = outBook.Sheet("jp") as XSSFSheet;//new XSSFSheet();//
var infoSheet = outBook.Sheet("info") as XSSFSheet;
var hrow = outSheet.GetRow(0);
var head = new ExcelHead(hrow);
//遍历文件列表输出到输出文件
// 每 MaxRowPerSheet 条分一张表
int MaxrowPerSheet = 50000;
int outSheetRowIdx = 0;
int outSheetIdx = 0;
int totalCellCount = 0;
int inFileIdx = 0;
int inSheetIdx = 0;
int MaxRowAndColumnNum = 90000;
long wordCount = 0;
long totalWordCount = 0;
foreach (var path in Pathes)
{
++inFileIdx;
IWorkbook inbook = ExcelUtils.Open(path);
inSheetIdx = 0;
var infoCell = infoSheet.Cell(inFileIdx, inSheetIdx);
infoCell.SetCellValue(path);
foreach (var sheet in inbook.AllSheets())
{
++inSheetIdx;
for (var ir = 1; ir <= sheet.LastRowNum && ir <= MaxRowAndColumnNum; ++ir)
{
var row = sheet.GetRow(ir);
if (row == null)
{
continue;
}
if ((ir % 100) == 0)
{
EditorUtility.DisplayCancelableProgressBar(
"CollectJp ..." + path.RReplace(".*ExcelData/", "")
, sheet.SheetName + ": " + ir + "/" + sheet.LastRowNum
, (float)(inFileIdx) / Pathes.Count());
}
for (int ic = 0; ic < row.LastCellNum && ic <= MaxRowAndColumnNum; ++ic)
{
// // 分表
// if (outSheetRowIdx > MaxrowPerSheet)
// {
// ++outSheetIdx;
// outSheetRowIdx = 0;
// outSheet = outBook.CreateSheet("jp_" + outSheetIdx) as XSSFSheet;
// // add head
// var h = outSheet.Row(0);
// h.RowStyle = hrow.RowStyle;
// for (var hi = 0; hi < hrow.LastCellNum; ++hi)
// {
// h.Cell(hi).SetCellValue(hrow.Cell(hi).SafeSValue());
// outSheet.SetColumnWidth(hi, 1500);
// }
// }
var v = row.Cell(ic).SValueOneline();
// AppLog.d(Tag, "{0}: {1}", v, v.Length);
var matches = Regex.Matches(v, JPRegular + "+.*");
if (matches.Count > 0)
{
++totalCellCount;
++outSheetRowIdx;
var c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.jp]);
// 去重引用
var iorow = outSheet.Contain(r => r.Cell(head[HeadIdx.jp]).SValueOneline() == v);
if (iorow > 0)
{
c.SetCellValue(string.Format("${0}", iorow));
}
else
{
wordCount += v.Length;
c.SetCellValue(v);
}
//c.CellStyle.IsLocked = cellLock;
totalWordCount += v.Length;
c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.trans]);
c.SetCellValue("译文");
//c.CellStyle.IsLocked = false;
c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.trans_jd]);
c.SetCellValue("校对");
//c.CellStyle.IsLocked = false;
c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.i]);
c.SetCellValue(ir);
//c.CellStyle.IsLocked = cellLock;
c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.j]);
c.SetCellValue(ic);
//c.CellStyle.IsLocked = cellLock;
c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.SheetName]);
c.SetCellValue(sheet.SheetName);
//c.CellStyle.IsLocked = cellLock;
c = outSheet.Cell(outSheetRowIdx, head[HeadIdx.FilePath]);
c.SetCellValue(path);
}
} // cell
} // row
// add file sheet info to info sheet
// one line per file
infoCell = infoSheet.Cell(inFileIdx, inSheetIdx);
infoCell.SetCellValue(sheet.SheetName);
} // sheet
} // path
infoSheet.Cell(inFileIdx + 1, 0).SetCellValue("wordCount");
infoSheet.Cell(inFileIdx + 1, 1).SetCellValue(wordCount);
var outStream = new FileStream(outExcelPath, FileMode.Create);
outBook.Write(outStream);
AppLog.d(Tag, "CollectJp: " + outExcelPath);
CountWordUniq = wordCount;
CountWord = totalWordCount;
}
finally
{
EditorUtility.ClearProgressBar();
}
}
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);
}
