public void TestStringFormula()
{
ExcelExtractor extractor = CreateExtractor("StringFormulas.xls");
// Comes out as NaN if treated as a number
// And as XYZ if treated as a string
Assert.AreEqual("Sheet1\nXYZ\nSheet2\nSheet3\n", extractor.Text);
extractor.FormulasNotResults = (true);
Assert.AreEqual("Sheet1\nUPPER(\"xyz\")\nSheet2\nSheet3\n", extractor.Text);
}
public void TestStringConcat()
{
ExcelExtractor extractor = CreateExtractor("SimpleWithFormula.xls");
// Comes out as NaN if treated as a number
// And as XYZ if treated as a string
Assert.AreEqual("Sheet1\nreplaceme\nreplaceme\nreplacemereplaceme\nSheet2\nSheet3\n", extractor.Text);
extractor.FormulasNotResults = (true);
Assert.AreEqual("Sheet1\nreplaceme\nreplaceme\nCONCATENATE(A1,A2)\nSheet2\nSheet3\n", extractor.Text);
}
public void Test45538()
{
String[] files =
{
"45538_classic_Footer.xls", "45538_form_Footer.xls",
"45538_classic_Header.xls", "45538_form_Header.xls"
};
for (int i = 0; i < files.Length; i++)
{
ExcelExtractor extractor = CreateExtractor(files[i]);
String text = extractor.Text;
Assert.IsTrue(text.IndexOf("testdoc") >= 0, "Unable to find expected word in text\n" + text);
Assert.IsTrue(text.IndexOf("test phrase") >= 0, "Unable to find expected word in text\n" + text);
}
}
public void TestFormatting()
{
ExcelExtractor extractor = CreateExtractor("Formatting.xls");
extractor.IncludeBlankCells = (false);
extractor.IncludeSheetNames = false;
String text = extractor.Text;
// Note - not all the formats in the file
// actually quite match what they claim to
// be, as some are auto-local builtins...
Assert.IsTrue(text.StartsWith(
"Dates, all 24th November 2006\n"
));
Assert.IsTrue(
text.IndexOf(
"yyyy/mm/dd\t2006/11/24\n"
) > -1
);
Assert.IsTrue(
text.IndexOf(
"yyyy-mm-dd\t2006-11-24\n"
) > -1
);
Assert.IsTrue(
text.IndexOf(
"dd-mm-yy\t24-11-06\n"
) > -1
);
Assert.IsTrue(
text.IndexOf(
"nn.nn\t10.52\n"
) > -1
);
Assert.IsTrue(
text.IndexOf(
"nn.nnn\t10.520\n"
) > -1
);
Assert.IsTrue(
text.IndexOf(
"\u00a3nn.nn\t\u00a310.52\n"
) > -1
);
}
public static string Parse(FileStream file)
{
string retVal = "";
try
{
HSSFWorkbook hssfwb = new HSSFWorkbook(file);
ExcelExtractor extractor = new ExcelExtractor(hssfwb);
retVal = extractor.Text;
}
catch (Exception ex)
{
ParsersInfra.RecordParsingFailure(Log, ex, file);
}
return(retVal);
}
/// <summary>
/// 测试NPOI组件方法得到*.xls文件第一张‘Sheet’的内容；未被用到的方法。
/// </summary>
/// <param name="FileName">文件完整的路径名，如D:\\test.xls</param>
/// <returns>返回FileName第一张‘Sheet’的内容</returns>
public static string npoiExtractStringTesting(string FileName)
{
try
{
FileInfo files = new FileInfo(FileName);
using (FileStream fs = files.Open(FileMode.Open))
{
HSSFWorkbook HBook = new HSSFWorkbook(fs);
ExcelExtractor extractor = new ExcelExtractor(HBook);
return(extractor.Text);
}
}
catch
{
FormMain.isSqlLangCreatedSuccessful = false;
return(null);
}
}
public void TestComparedToOLE2()
{
// A fairly simple file - ooxml
XSSFExcelExtractor ooxmlExtractor = GetExtractor("SampleSS.xlsx");
ExcelExtractor ole2Extractor =
new ExcelExtractor(HSSFTestDataSamples.OpenSampleWorkbook("SampleSS.xls"));
POITextExtractor[] extractors =
new POITextExtractor[] { ooxmlExtractor, ole2Extractor };
for (int i = 0; i < extractors.Length; i++)
{
POITextExtractor extractor = extractors[i];
String text = Regex.Replace(extractor.Text, "[\r\t]", "");
Assert.IsTrue(text.StartsWith("First Sheet\nTest spreadsheet\n2nd row2nd row 2nd column\n"));
Regex pattern = new Regex(".*13(\\.0+)?\\s+Sheet3.*", RegexOptions.Compiled);
Assert.IsTrue(pattern.IsMatch(text));
}
}
public void ExtractTest()
{
var ee = new ExcelExtractor();
var res = ee.ExtractScheduleItems();
var exp = new ScheduleItem()
{
Leader = "Ula",
Name = "AKTYWNY SENIOR",
Place = new Place()
{
Address = "jupiter"
},
Time = new DateTime(1, 1, 1, 9, 0, 0)
};
Assert.AreEqual(exp.Time, res[0].Time);
Assert.AreEqual(exp.Place.Address, res[0].Place.Address);
Assert.AreEqual(exp.Name, res[0].Name);
Assert.AreEqual(exp.Leader, res[0].Leader);
}
private void transitTimesButton_Click(object sender, EventArgs e)
{
if (!this.EnsureFieldsFilled())
{
return;
}
this.progressBar.Value = 10;
IList <FilmDetails> films = new ExcelExtractor()
{
SpreadsheetLocation = this.infoDatabaseTextBox.Text
}.ExtractFilms();
this.progressBar.Value = 60;
new ShippingCalculator()
{
OutputFilePath = (this.outputFolderTextBox.Text + (this.outputFolderTextBox.Text.EndsWith("\\") ? string.Empty : "\\"))
}.Execute(films);
this.progressBar.Value = 100;
int num = (int)MessageBox.Show("I have calculated the transit times", "Success", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
public void TestWithBlank()
{
ExcelExtractor extractor = CreateExtractor("MissingBits.xls");
String def = extractor.Text;
extractor.IncludeBlankCells = (true);
String padded = extractor.Text;
Assert.IsTrue(def.StartsWith(
"Sheet1\n" +
"&[TAB]\t\n" +
"Hello\t\n" +
"11\t23\t\n"
));
Assert.IsTrue(padded.StartsWith(
"Sheet1\n" +
"&[TAB]\t\n" +
"Hello\t\t\t\t\t\t\t\t\t\t\t\n" +
"11\t\t\t23\t\t\t\t\t\t\t\t\n"
));
}
public void ExcelExtractorTest()
{
try
{
ExcelExtractor excelExtractor = new ExcelExtractor();
PipelineContext pipelineContext = new PipelineContext();
pipelineContext.SourceFilePath = Environment.CurrentDirectory.Replace(@"bin\Debug",
@"ExcelExtractorTest.xlsx");
pipelineContext.FirstLineContainsHeaders = true;
pipelineContext.ExcelWorksheetName = "Sheet1";
List <object[]> ResultList = new List <object[]>();
List <string> ConcatenatedResult = new List <string>();
Action <PipelineContext, IProducerConsumerCollection <object[]>, ManualResetEvent> function = excelExtractor.GetPausableWorkItem();
ConcurrentQueue <object[]> results = new ConcurrentQueue <object[]>();
function(pipelineContext, results, null);
ResultList = results.ToList();
StringBuilder sb = new StringBuilder();
foreach (string[] s in ResultList)
{
sb.Clear();
foreach (string p in s)
{
Console.Write(p);
sb.Append(p);
}
Console.WriteLine();
ConcatenatedResult.Add(sb.ToString());
}
Assert.AreEqual(expected: "NishaSahuSweetheart :PIndia", actual: ConcatenatedResult[0]);
Assert.AreEqual(expected: "JasperRisBreaker of SQL serversNetherlands", actual: ConcatenatedResult[1]);
}
catch (System.Runtime.InteropServices.COMException)
{
//if excel //office interop or whatever is not installed and registered on the build machine the test will flunk. to prevent this we will catch the comexception and assert inconclusive
// Assert.Inconclusive();
}
}
public void TestWithComments()
{
ExcelExtractor extractor = CreateExtractor("SimpleWithComments.xls");
extractor.IncludeSheetNames = (false);
// Check without comments
Assert.AreEqual(
"1\tone\n" +
"2\ttwo\n" +
"3\tthree\n",
extractor.Text
);
// Now with
extractor.IncludeCellComments = (true);
Assert.AreEqual(
"1\tone Comment by Yegor Kozlov: Yegor Kozlov: first cell\n" +
"2\ttwo Comment by Yegor Kozlov: Yegor Kozlov: second cell\n" +
"3\tthree Comment by Yegor Kozlov: Yegor Kozlov: third cell\n",
extractor.Text
);
}
private static string GetExcelContent(string path)
{
string fileExtension = Path.GetExtension(path).ToLower();
if (fileExtension.Contains("xlsx"))
{
StringBuilder sb = new StringBuilder();
SpreadsheetDocument xlsx = SpreadsheetDocument.Open(path, false);
foreach (OpenXmlElement item in xlsx.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <OpenXmlElement>())
{
sb.Append(item.InnerText).Append(" ");
}
return(sb.ToString());
}
else
{
FileStream fs = new FileStream(path, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
ExcelExtractor extractor = new ExcelExtractor(wb);
fs.Close();
return(extractor.Text);
}
}
public void TestWithEmbeded()
{
POIFSFileSystem fs = new POIFSFileSystem(
POIDataSamples.GetDocumentInstance().OpenResourceAsStream("word_with_embeded.doc")
);
DirectoryNode objPool = (DirectoryNode)fs.Root.GetEntry("ObjectPool");
DirectoryNode dirA = (DirectoryNode)objPool.GetEntry("_1269427460");
DirectoryNode dirB = (DirectoryNode)objPool.GetEntry("_1269427461");
HSSFWorkbook wbA = new HSSFWorkbook(dirA, fs, true);
HSSFWorkbook wbB = new HSSFWorkbook(dirB, fs, true);
ExcelExtractor exA = new ExcelExtractor(wbA);
ExcelExtractor exB = new ExcelExtractor(wbB);
Assert.AreEqual("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n",
exA.Text);
Assert.AreEqual("Sample Excel", exA.SummaryInformation.Title);
Assert.AreEqual("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n",
exB.Text);
Assert.AreEqual("Sample Excel 2", exB.SummaryInformation.Title);
}
private string ExtractDateFolderOrFilePath(string dateFolder, ref ExcelExtractor._2_model.DirectoryModel dir)
{
return dateFolder.Replace(dir.Source, "");
}
public void TestConstructors()
{
POIFSFileSystem fs;
HSSFWorkbook wb;
try
{
fs = new POIFSFileSystem(_samples.OpenResourceAsStream("TestUnicode.xls"));
wb = new HSSFWorkbook(fs);
}
catch (IOException e)
{
throw new Exception("TestConstructors", e);
}
ExcelExtractor excelExt = new ExcelExtractor(wb);
String fsText;
HPSFPropertiesExtractor fsExt = new HPSFPropertiesExtractor(fs);
fsExt.SetFilesystem(null); // Don't close re-used test resources!
try
{
fsText = fsExt.Text;
}
finally
{
fsExt.Close();
}
String hwText;
HPSFPropertiesExtractor hwExt = new HPSFPropertiesExtractor(wb);
hwExt.SetFilesystem(null); // Don't close re-used test resources!
try
{
hwText = hwExt.Text;
}
finally
{
hwExt.Close();
}
String eeText;
HPSFPropertiesExtractor eeExt = new HPSFPropertiesExtractor(excelExt);
eeExt.SetFilesystem(null); // Don't close re-used test resources!
try
{
eeText = eeExt.Text;
}
finally
{
eeExt.Close();
}
Assert.AreEqual(fsText, hwText);
Assert.AreEqual(fsText, eeText);
Assert.IsTrue(fsText.IndexOf("AUTHOR = marshall") > -1);
Assert.IsTrue(fsText.IndexOf("TITLE = Titel: \u00c4h") > -1);
// Finally tidy
wb.Close();
}
private void ProcessFile(ref BackgroundWorker wk, ref ExcelExtractor._2_model.DirectoryModel dir, ref Application excel, string xlsxFile)
{
try
{
if (!directoryCtrl.IsTheDirectoryExist(xlsxFile, dir.Source, dir.Destination))
directoryCtrl.CreateDirectory(xlsxFile, dir.Source, dir.Destination);
if (!xlsFileCtrl.IsFileExist(xlsxFile, dir.Source, dir.Destination))
{
wk.ReportProgress(0, Constants.PROC_PROCESSING_FILE + ExtractDateFolderOrFilePath(xlsxFile, ref dir));
xlsFileCtrl.CreateXLSFile(ref wk, ref excel, xlsxFile, dir.Source, dir.Destination);
}
else
wk.ReportProgress(0, Constants.PROC_SKIP_FILE + ExtractDateFolderOrFilePath(xlsxFile, ref dir));
}
catch
{
throw;
}
}
public void StartExtraction(ref BackgroundWorker wk, ref DoWorkEventArgs e, ref ExcelExtractor._2_model.DirectoryModel dir)
{
wk.ReportProgress(0, Constants.PROC_START);
Application excel = null;
List<string> sortedDirectoryList = null;
try
{
excel = new Application();
excel.FileValidation = MsoFileValidationMode.msoFileValidationSkip;
excel.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
excel.Visible = false;
excel.ScreenUpdating = false;
}
catch(Exception ex)
{
throw new Exception(Constants.PROC_OPEN_APP + Constants.PROC_ACTUAL_ERROR + ex.Message);
}
try
{
bool shouldWriteToLastProc = true;
sortedDirectoryList = SortDirectoryListByDate(Directory.GetDirectories(dir.Source).ToList());
foreach (string dateFolder in sortedDirectoryList)
{
wk.ReportProgress(0, Constants.PROC_CHECK_FOLDER + ExtractDateFolderOrFilePath(dateFolder, ref dir));
if (DateTime.Compare(DateTime.ParseExact(directoryCtrl.ExtractFolderName(dateFolder), Constants.SYS_DATE_FORMATE, CultureInfo.InvariantCulture),
DateTime.ParseExact(lastProcCtrl.ReadLastProc(), Constants.SYS_DATE_FORMATE, CultureInfo.InvariantCulture)) >= 0)
{
wk.ReportProgress(0, Constants.PROC_PROCESSING_FOLDER + ExtractDateFolderOrFilePath(dateFolder, ref dir));
foreach (string xlsxFile in Directory.EnumerateFiles(dateFolder, Constants.SYS_XLS_FORMAT, SearchOption.AllDirectories).ToList())
{
if (wk.CancellationPending)
{
wk.ReportProgress(0, Constants.SYS_CLEAR_CLIPBOARD);
excel.Quit();
e.Cancel = true;
return;
}
try
{
wk.ReportProgress(0, Constants.PROC_CHECK_FILE + ExtractDateFolderOrFilePath(xlsxFile, ref dir));
ProcessFile(ref wk, ref dir, ref excel, xlsxFile);
}
catch (Exception ex)
{
shouldWriteToLastProc = false;
wk.ReportProgress(0, ex.Message);
}
}
if(shouldWriteToLastProc) //Any file inside any folder fail to process, should be process again
lastProcCtrl.WriteLastProc(dateFolder);
}
else
wk.ReportProgress(0, Constants.PROC_SKIP_FOLDER + ExtractDateFolderOrFilePath(dateFolder, ref dir));
}
excel.Quit();
}
catch (Exception ex)
{
excel.Quit();
wk.ReportProgress(0, ex.Message);
}
finally
{
if(excel != null) Marshal.FinalReleaseComObject(excel);
}
}
//解释成lua
private void readParseToLua(string path, ref StringBuilder sb)
{
using (FileStream fs = File.OpenRead(path)) //打开myxls.xls文件
{
HSSFWorkbook wk = new HSSFWorkbook(fs);
ExcelExtractor extractor = new ExcelExtractor(wk);
ISheet sheet = wk.GetSheetAt(0);
string xmlName = Path.GetFileNameWithoutExtension(path); //Lua_x_config.xml
string className = xmlName.Substring(6, xmlName.Length - 6); //lua_s_
LogtextBox.Text += string.Format("处理表{0}\r\n", xmlName);
//第一行
IRow row0 = sheet.GetRow(1);
IRow row1 = sheet.GetRow(2);
int clomn_Count = row0.LastCellNum;
int row_Count = sheet.LastRowNum + 1;
List <int> rList = new List <int>(); //有效的列号
List <string> pList = new List <string>(); //属性名
List <string> tList = new List <string>(); //类型名
//记录属性
for (int i = 0, len = clomn_Count; i < len; i++)
{
string pName = row0.GetCell(i).ToString();
string tName = row1.GetCell(i).ToString();
if (string.IsNullOrEmpty(pName))
{
string strtips = string.Format("{0}表 {1}列 属性无效", xmlName, i);
MessageBox.Show(strtips);
return;
}
if (string.IsNullOrEmpty(tName))
{
string strtips = string.Format("{0}表 {1}列 数据类型无效", xmlName, i);
MessageBox.Show(strtips);
return;
}
pList.Add(pName);
tList.Add(tName);
rList.Add(i);
}
string localDListStr = tList[0] == "string" ? "DList_string_LuaInterface_LuaTable" : "DList_int_LuaInterface_LuaTable";
sb.AppendLine(string.Format("dList = {0}.New()", localDListStr));
sb.AppendLine(string.Format("LuaCfgData.{0} = dList;", className));
//记录数据
for (int i = 3; i < row_Count; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
ICell v = row.GetCell(rList[0]);
if (v == null)
{
continue;
}
string str = "";
//关键id
for (int j = 0, jlen = rList.Count; j < jlen; j++)
{
int ii = rList[j];
v = row.GetCell(ii);
if (tList[j] == "string")
{
str += string.Format("{0}=\"{1}\";", pList[ii], v);
}
else if (tList[j] == "int[]")
{
str += string.Format("{0}={1};", pList[ii], "{" + v + "}");
}
else if (tList[j] == "int")
{
if (string.IsNullOrEmpty(v.ToString()))
{
str += string.Format("{0}=0;", pList[ii]);
}
else
{
str += string.Format("{0}={1};", pList[ii], v);
}
}
else
{
str += string.Format("{0}={1};", pList[ii], v);
}
}
//写lua
string dbStr = tList[0] == "string" ? "dList:Add(\"{0}\" , {1});" : "dList:Add({0} , {1});";
sb.AppendLine(string.Format(dbStr, row.GetCell(rList[0]), "{" + str + "}"));
}
LogtextBox.Text += string.Format("处理表{0}结束\r\n", xmlName);
}
}
public void readParse(string path)
{
bool isCan = true;
using (FileStream fs = File.OpenRead(path)) //打开myxls.xls文件
{
StringBuilder classSourceTemp = new StringBuilder();
DataTable objectData = new DataTable();
HSSFWorkbook wk = new HSSFWorkbook(fs);
ExcelExtractor extractor = new ExcelExtractor(wk);
ISheet sheet = wk.GetSheetAt(0);
string className = Path.GetFileNameWithoutExtension(path);
LogtextBox.Text += string.Format("处理表{0}\r\n", className);
string propertyName, propertyType;
//第一行
IRow row0 = sheet.GetRow(1);
IRow row1 = sheet.GetRow(2);
int clomn_Count = row0.LastCellNum;
int row_Count = sheet.LastRowNum + 1;
List <string> columnHead = new List <string>();
List <string> columnType = new List <string>();
classSourceTemp.Append("using System;\n");
classSourceTemp.Append("[Serializable]\n");
classSourceTemp.AppendFormat("public class {0} \n", className);
classSourceTemp.Append("{\n");
classSourceTemp.AppendFormat("public {0}(){{}}\n", className);
for (int j = 0, len = clomn_Count; j < len; j++)
{
propertyName = row0.GetCell(j).ToString();
columnHead.Add(propertyName);
propertyType = row1.GetCell(j).ToString();
columnType.Add(propertyType);
if (propertyName.Equals("") || propertyType.Equals(""))
{
continue;
}
classSourceTemp.AppendFormat(" public {0} {1} ;\n", propertyType, propertyName);
//classSourceTemp.Append(" public " + propertyType + " " + "" + propertyName + "\n");
//classSourceTemp.Append(" {\n");
//classSourceTemp.Append(" get{ return _" + propertyName + ";} \n");
//classSourceTemp.Append(" set{ _" + propertyName + " = value; }\n");
classSourceTemp.Append(" }\n");
//classSource.Append("\tpublic " + ((Excel.Range)m_Worksheet.Cells[4, j]).Text.ToString() + " " + ((Excel.Range)m_Worksheet.Cells[3, j]).Text.ToString() + ";\n");
}
classSourceTemp.Append("}\n");
initDataTable(columnHead, columnType, row_Count, objectData);
for (int i = 3; i < row_Count; i++)//
{
IRow curow = sheet.GetRow(i);
if (curow == null)
{
continue;
}
DataRow dr = objectData.NewRow();
for (int j = 0; j < clomn_Count; j++)
{
if (isCan)
{
try
{
if (curow.GetCell(j) != null)
{
string item;
if (curow.GetCell(j).CellType == CellType.Formula)
{
CellType type = curow.GetCell(j).CachedFormulaResultType;
switch (type)
{
case CellType.Numeric:
{
item = curow.GetCell(j).NumericCellValue.ToString();
}
break;
default:
item = curow.GetCell(j).StringCellValue.ToString();
break;
}
}
else
{
item = curow.GetCell(j).ToString();
}
objectData.Rows[i - 2][j] = item;
}
else
{
objectData.Rows[i - 2][j] = "";
}
}
catch (Exception ex)
{
string strtips = string.Format("表{0}发生错误\n\t\t 行{1}列{2}错误{3}", className, i, j, ex.Message);
MessageBox.Show(strtips);
isCan = false;
}
}
}
}
// if (dataOutputPathIndex == 1 || dataOutputPathIndex == 3)
// {
// seriData(className, objectData, DataOutPutPath);
// }
LogtextBox.Text += string.Format("处理表{0}结束\r\n", className);
}
}
private void readTitle(string path, StringBuilder sData, StringBuilder sBaseData)
{
using (FileStream fs = File.OpenRead(path)) //打开myxls.xls文件
{
HSSFWorkbook wk = new HSSFWorkbook(fs);
ExcelExtractor extractor = new ExcelExtractor(wk);
ISheet sheet = wk.GetSheetAt(0);
string className = Path.GetFileNameWithoutExtension(path);
string propertyName, propertyType, info;
//第一行
IRow rowName = sheet.GetRow(0);
IRow row0 = sheet.GetRow(1);
IRow row1 = sheet.GetRow(2);
int clomn_Count = row0.LastCellNum;
int row_Count = sheet.LastRowNum + 1;
List <string> columnHead = new List <string>();
List <string> columnType = new List <string>();
List <string> creatList = new List <string>();
List <string> creatList2 = new List <string>();
sData.AppendFormat("{0}:Map<number,{0}>=new Map<number,{0}>();\n", className);
sBaseData.AppendFormat("//{0} \n", className);
classSource.AppendFormat("export class {0} \n", className);
classSource.Append("{\n");
// classSource.AppendFormat("public {0}(){{}}\n", className);
string creatStr;
string creatStr2;
for (int j = 0, len = clomn_Count; j < len; j++)
{
info = rowName.GetCell(j).ToString();
info = info.Replace("\n", " ");
propertyName = row0.GetCell(j).ToString();
columnHead.Add(propertyName);
propertyType = row1.GetCell(j).ToString();
switch (propertyType)
{
case "int":
case "float":
propertyType = "number";
break;
case "int[]":
case "float[]":
propertyType = "number[]";
break;
case "string":
propertyType = "string";
break;
case "string[]":
propertyType = "string[]";
break;
case "bool":
propertyType = "boolean";
break;
}
columnType.Add(propertyType);
if (propertyName.Equals("") || propertyType.Equals(""))
{
continue;
}
classSource.AppendFormat("//{0} \n", info);
classSource.AppendFormat(" public {0}:{1} ;\n", propertyName, propertyType);
creatStr = string.Format("{0}:{1}", propertyName, propertyType);
creatList.Add(creatStr);
creatStr2 = string.Format("data.{0}={0}; \n", propertyName);
creatList2.Add(creatStr2);
}
classSource.Append("public static Creat(");
for (int j = 0; j < creatList.Count; j++)
{
if (j + 1 == creatList.Count)
{
classSource.AppendFormat("{0} ):{1}", creatList[j], className);
classSource.Append("{\n");
classSource.AppendFormat("let data:{0}=new {0}(); \n", className);
for (int k = 0; k < creatList2.Count; k++)
{
classSource.Append(creatList2[k]);
}
classSource.Append("return data; \n");
classSource.Append("}\n");
}
else
{
classSource.AppendFormat("{0},", creatList[j], className);
}
}
//创建类方法;
classSource.Append("}\n");
//数据
for (int i = 3; i < row_Count; i++)//
{
IRow curow = sheet.GetRow(i);
if (curow == null)
{
continue;
}
for (int j = 0; j < clomn_Count; j++)
{
propertyName = row0.GetCell(j).ToString();
propertyType = row1.GetCell(j).ToString();
switch (propertyType)
{
case "int":
case "float":
propertyType = "number";
break;
case "int[]":
case "float[]":
propertyType = "number[]";
break;
case "string":
propertyType = "string";
break;
case "string[]":
propertyType = "string[]";
break;
case "bool":
propertyType = "boolean";
break;
}
if (propertyName.Equals("") || propertyType.Equals(""))
{
if (j + 1 == clomn_Count)
{
sBaseData.Append(")); \n");
}
continue;
}
string itemValue;
if (curow.GetCell(j) != null)
{
if (curow.GetCell(j).CellType == CellType.Formula)
{
CellType type = curow.GetCell(j).CachedFormulaResultType;
switch (type)
{
case CellType.Numeric:
{
itemValue = curow.GetCell(j).NumericCellValue.ToString();
}
break;
default:
itemValue = curow.GetCell(j).StringCellValue.ToString();
break;
}
}
else
{
itemValue = curow.GetCell(j).ToString();
}
}
else
{
itemValue = "";
}
if (j == 0)
{
//补个头;
sBaseData.AppendFormat("this.{0}.set({1},{0}.Creat(", className, itemValue);
}
else
{
sBaseData.Append(",");
}
switch (propertyType)
{
case "number":
if (itemValue == "")
{
sBaseData.Append("0");
}
else
{
sBaseData.AppendFormat("{0}", itemValue);
}
break;
case "number[]":
//多维数组;
if (itemValue == "")
{
sBaseData.Append("[]");
}
else
{
sBaseData.AppendFormat("[{0}]", itemValue);
}
break;
case "string[]":
//多维数组;
if (itemValue == "")
{
sBaseData.Append("[]");
}
else
{
string[] sst = itemValue.Split(',');
StringBuilder vvStr = new StringBuilder();
vvStr.Append("[");
for (int oo = 0; oo < sst.Length; oo++)
{
vvStr.AppendFormat("'{0}'", sst[oo]);
if (oo + 1 < sst.Length)
{
vvStr.Append(",");
}
}
vvStr.Append("]");
sBaseData.Append(vvStr.ToString());
}
break;
case "string":
if (itemValue == "")
{
sBaseData.Append("''");
}
else
{
sBaseData.AppendFormat("'{0}'", itemValue);
}
break;
case "boolean":
if (itemValue == "")
{
sBaseData.Append("false");
}
else
{
if (itemValue == "0")
{
sBaseData.Append("false");
}
else
{
sBaseData.Append("true");
}
}
break;
}
if (j + 1 == clomn_Count)
{
sBaseData.Append(")); \n");
}
}
sBaseData.Append("\n");
}
}
}
