public int getRowCount(string sheetName)
{
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(0);
}
else
{
sheet = workbook.GetSheetAt(index);
int number = sheet.LastRowNum + 1;
return(number);
}
}
public int getRowNumber(string sheetName, int colNum, string value)
{
try
{
fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
workbook = new XSSFWorkbook(fs);
int d = 0;
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(0);
}
sheet = workbook.GetSheetAt(index);
for (int rw = 0; rw < sheet.LastRowNum; rw++)
{
if (sheet.GetRow(rw) != null)
{
row = sheet.GetRow(rw);
}
}
return(row.RowNum);
}
catch (Exception)
{
return(row.RowNum);
}
}
public static List <string> GetCellDataList(string sheetName, string colName)
{
string path = @"D:\SeleniumCsharp\AmazonTestFramework\AmazonTest\Data\AmazonExcelData.xlsx";
Console.WriteLine(Path.GetFullPath(path));
XSSFWorkbook wb;
List <string> data = new List <string>();
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
wb = new XSSFWorkbook(fs);
int index = wb.GetSheetIndex(sheetName);
ISheet sh = wb.GetSheetAt(index);
int totalRow = sh.LastRowNum;
for (int i = 0; i <= totalRow; i++)
{
IRow row = sh.GetRow(i);
ICell cell1 = row.GetCell(0);
string cell1Value = cell1.StringCellValue;
if (cell1.StringCellValue.Equals(colName))
{
int totalColumn = row.LastCellNum;
for (int j = 1; j < totalColumn; j++)
{
ICell cell = row.GetCell(j);
data.Add(cell.StringCellValue);
}
break;
}
}
wb.Close();
fs.Close();
return(data);
}
/// <summary>
/// 缓存某个表
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="sheetName">表格名称</param>
private void CacheData(DataTable dt, string sheetName)
{
if (dt == null)
{
return;
}
//新建sheet
var sheet = _workbook.CreateSheet(sheetName);//名为ref的工作表
var index = _workbook.GetSheetIndex(sheet);
var row = sheet.CreateRow(0);
ICell cell;
//写入表头
var colIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
cell = row.CreateCell(colIndex++);
cell.SetCellValue(dc.ColumnName);
//设置单元格为字符串类型，便于取值
cell.SetCellType(CellType.String);
}
//写入数据行
var rowIndex = 1;
//循环赋值
foreach (DataRow dr in dt.Rows)
{
row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
colIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
cell = row.CreateCell(colIndex++);
cell.SetCellValue(dr[dc.ColumnName].ToString());
//设置单元格为字符串类型，便于取值
cell.SetCellType(CellType.String);
}
rowIndex++;
}
//隐藏缓存数据的列
//调试模式下，显示该Excel
_workbook.SetSheetHidden(index, _config.Prop.Debug ? false : true);
}
public string setCellData(string sheetName, string colName, int rowNum, string data)
{
try
{
fs = new FileStream(path, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(fs);
if (rowNum <= 0)
{
return("");
}
int colNum = -1;
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return("");
}
sheet = workbook.GetSheetAt(index);
row = sheet.GetRow(0);
for (int i = 0; i < row.LastCellNum; i++)
{
if (row.GetCell(i).StringCellValue.Equals(colName))
{
colNum = i;
}
}
if (colNum == -1)
{
return("");
}
row = sheet.GetRow(rowNum - 1);
if (row == null)
{
row = sheet.CreateRow(rowNum - 1);
}
cell = row.GetCell(colNum);
if (cell == null)
{
cell = row.CreateCell(colNum);
}
ICellStyle cs = workbook.CreateCellStyle();
cs.WrapText = true;
cell.CellStyle = cs;
cell.SetCellValue(data);
FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(f);
f.Close();
fs.Close();
return(data);
}
catch (Exception)
{
return("");
}
}
public void SetFirstVisibleTab_57373()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
/*Sheet sheet1 =*/
wb.CreateSheet();
ISheet sheet2 = wb.CreateSheet();
int idx2 = wb.GetSheetIndex(sheet2);
ISheet sheet3 = wb.CreateSheet();
int idx3 = wb.GetSheetIndex(sheet3);
// add many sheets so "first visible" is relevant
for (int i = 0; i < 30; i++)
{
wb.CreateSheet();
}
wb.FirstVisibleTab = (/*setter*/ idx2);
wb.SetActiveSheet(idx3);
//wb.Write(new FileOutputStream(new File("C:\\temp\\test.xlsx")));
Assert.AreEqual(idx2, wb.FirstVisibleTab);
Assert.AreEqual(idx3, wb.ActiveSheetIndex);
IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet2 = wbBack.GetSheetAt(idx2);
sheet3 = wbBack.GetSheetAt(idx3);
Assert.AreEqual(idx2, wb.FirstVisibleTab);
Assert.AreEqual(idx3, wb.ActiveSheetIndex);
wbBack.Close();
}
finally
{
wb.Close();
}
}
public string setCellData(string sheetName, int colNum, int rowNum, string data)
{
fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
workbook = new XSSFWorkbook(fs);
if (rowNum <= 0)
{
return("");
}
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return("");
}
sheet = workbook.GetSheetAt(index);
row = sheet.GetRow(rowNum - 1);
if (row == null)
{
row = sheet.CreateRow(rowNum - 1);
}
cell = row.GetCell(colNum - 1);
if (cell == null)
{
cell = row.CreateCell(colNum - 1);
}
//for (int rownum = 1; rownum <= 1000; rownum++)
//{
// row = sheet.CreateRow(rownum);
// cell = row.CreateCell(colNum-1);
// ICellStyle cs = workbook.CreateCellStyle();
// cs.WrapText = true;
// cell.CellStyle = cs;
// cell.SetCellValue(data);
//}
ICellStyle cs = workbook.CreateCellStyle();
cs.WrapText = true;
cell.CellStyle = cs;
cell.SetCellValue(data);
FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(f);
f.Close();
fs.Close();
return(data);
}
//以下未优化
/// <summary>
/// 将DataTable写入模板
/// </summary>
/// <param name="dataTable">汇总</param>
/// <param name="rowIndex">开始行</param>
/// <param name="colIndex">结束行</param>
/// <param name="shtName">要汇总的工作表名</param>
/// <param name="path">输出路径</param>
public static void DataTableToTemplate(DataTable dataTable, int rowIndex, int colIndex, string shtName, string path, params int[] nums)
{
IWorkbook wb;
using (FileStream fs = new FileStream("Template.xlsx", FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
{
wb = new XSSFWorkbook(fs);
}
ISheet sht = wb.GetSheetAt(wb.GetSheetIndex(shtName));
ICellStyle style = wb.CreateCellStyle();
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int r = 0; r < dataTable.Rows.Count; r++)
{
IRow row = sht.GetRow(rowIndex);
int col = colIndex;
for (int c = 0; c < dataTable.Columns.Count; c++)
{
ICell cell = row.CreateCell(col);
cell.CellStyle = style;
if (nums.Contains(c))
{
double.TryParse(dataTable.Rows[r][c].ToString(), out double d);
cell.SetCellValue(d);
}
else
{
Type type = dataTable.Columns[c].DataType;
SetCellTypeValue(type, dataTable.Rows[r][c].ToString(), ref cell);
}
col++;
}
rowIndex++;
}
MemoryStream stream = new MemoryStream();
wb.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs1 = new FileStream(path, FileMode.Create, FileAccess.Write))
{
fs1.Write(buf, 0, buf.Length);
fs1.Flush();
}
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
public bool addColumn(string sheetName, string colName)
{
try
{
fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook = new XSSFWorkbook(fs);
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(false);
}
ICellStyle cs = workbook.CreateCellStyle();
sheet = workbook.GetSheetAt(index);
row = sheet.GetRow(0);
if (row == null)
{
row = sheet.CreateRow(0);
}
cell = row.GetCell(0);
if (cell == null)
{
cell = row.CreateCell(0);
}
else
{
cell = row.CreateCell(row.LastCellNum);
}
cell.SetCellValue(colName);
cell.CellStyle = cs;
FileStream f = new FileStream(path, FileMode.Create);
workbook.Write(f);
f.Close();
fs.Close();
return(true);
}
catch (Exception)
{
return(false);
}
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, String oldName, String newName)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
public int GetSheetIndex(string name)
{
return(XssfWorkbook.GetSheetIndex(name));
}
public override void PassDataToExcel(ISmartTool tool)
{
TemplateFileName = "ARC Diagram.xlsx";
var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}";
try
{
using (
var file =
new FileStream(filePath,
FileMode.Open, FileAccess.Read))
{
Book = new XSSFWorkbook(file);
}
Sheet = Book.GetSheetAt(0);
Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{tool.Top.Name}_{tool.Top.SerialNumber}");
var arcData = new ArcData();
if (!arcData.Tools.ContainsKey(tool.Top.SerialNumber))
{
MessageBox.Show("No such ARC in library", "Information", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
return;
}
var arcTool = arcData.Tools[tool.Top.SerialNumber];
var inspectionLength = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Top.Length));
var arrayLength = Convert.ToSingle(arcTool.L, CultureInfo.InvariantCulture);
var difference = Math.Abs(inspectionLength - arrayLength);
if (difference > 0.025f)
{
MessageBox.Show($"Collar length {inspectionLength} doesn't match. Should be about {arrayLength}. Difference is {difference}. Prepare fishing diagram manually.", "Information", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
return;
}
//Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0)
//Поэтому от номера строки и столбца нужно отнимать 1
var cellNum = 2;
//ARC SerialNumber
SetCellValue(7, cellNum, tool.Top.SerialNumber);
//Bottom Sub SerialNumber
SetCellValue(11, cellNum, tool.Bottom.SerialNumber);
cellNum = 5;
//OD8 from ARC inspection
SetCellValue(13, cellNum, tool.Top.ConnectionOne.Od);
//OD7
SetCellValue(19, cellNum, arcTool.Od7);
//OD6
SetCellValue(22, cellNum, arcTool.Od6);
//OD5
SetCellValue(25, cellNum, arcTool.Od5);
//OD4
SetCellValue(36, cellNum, arcTool.Od4);
//OD3
SetCellValue(48, cellNum, arcTool.Od3);
//OD2
SetCellValue(60, cellNum, arcTool.Od2);
//OD1
SetCellValue(64, cellNum, arcTool.Od1);
//OD Saver Sub
SetCellValue(68, cellNum, tool.Bottom.ConnectionOne.Od);
//ID Saver Sub
SetCellValue(75, 7, tool.Bottom.ConnectionTwo.Id);
cellNum = 9;
//Top connection type
SetCellValue(7, cellNum, tool.Top.ConnectionOne.ConnectionType);
//Saver sub connection type
SetCellValue(75, cellNum, tool.Bottom.ConnectionTwo.ConnectionType);
cellNum = 10;
var saverSubLength = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Bottom.Length));
//L10
SetCellValue(18, cellNum, (Convert.ToSingle(arcTool.L10, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L9
SetCellValue(21, cellNum, (Convert.ToSingle(arcTool.L9, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L8
SetCellValue(23, cellNum, (Convert.ToSingle(arcTool.L8, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L7
SetCellValue(25, cellNum, (Convert.ToSingle(arcTool.L7, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L6
SetCellValue(35, cellNum, (Convert.ToSingle(arcTool.L6, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L5
SetCellValue(38, cellNum, (Convert.ToSingle(arcTool.L5, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L4
SetCellValue(47, cellNum, (Convert.ToSingle(arcTool.L4, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L3
SetCellValue(50, cellNum, (Convert.ToSingle(arcTool.L3, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L2
SetCellValue(59, cellNum, (Convert.ToSingle(arcTool.L2, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L1
SetCellValue(62, cellNum, (Convert.ToSingle(arcTool.L1, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000"));
//L total
SetCellValue(40, 13, (inspectionLength + saverSubLength).ToString("0.000"));
string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{
tool.Top.Name}_{tool.Top.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx";
//Сохранение изменённого файла
using (
var file =
new FileStream(fileName, FileMode.CreateNew, FileAccess.Write))
{
Book.Write(file);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "I have a bad feeling about this", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
}
//解析excel
public static List <Manage_Type> RecursionAnalyseExecel()
{
//1.读取
FileStream fs = File.OpenRead(@"C:\\work\222.xlsx");
//1.1获取execel文档
IWorkbook workBook = new XSSFWorkbook(fs);
ISheet sheet = null;
IRow row = null;
ICell cell = null;
//实例文档表数量
var countSheet = workBook.NumberOfSheets;
var countRow = 0; // sheet.LastRowNum;
var countCell = 0; // row.LastCellNum;
var thePointedSheet = 0;
Manage_Type model = new Manage_Type();
List <Manage_Type> listModel = new List <Manage_Type>();
var rootId = "1d9e56bd-3064-4dc8-aeef-5c4479903b05";
string[] ApproverAccount = { "Corporate Manuals", "Global & Site Specific Procedures",
"Global & Site Specific Regulations", "Operation Instructions", "Technical Specifications", "SOP" };
var arrTemp = new Manage_Type[10];
arrTemp[0] = new Manage_Type()
{
Id = rootId, ApproverAccount = ApproverAccount[0]
};
//数据表对象
sheet = workBook.GetSheetAt(0);
for (int i = 0; i < countSheet; i++)
{
//获取当前sheet
sheet = workBook.GetSheetAt(i);
//设置制定页
if (thePointedSheet != 0)
{
sheet = workBook.GetSheetAt(thePointedSheet);
countSheet = 0;
}
countRow = sheet.LastRowNum;
for (int j = 1; j <= countRow; j++)
{
row = sheet.GetRow(j);
countCell = row.Cells.Count;
bool isNewRow = true;
//判断是否为根目录
isNewRow = IsRootPath(row);
//添加列
for (int m = 1; m <= countCell; m++)
{
cell = row.GetCell(m);
if (cell == null || string.IsNullOrEmpty(cell.StringCellValue))
{
continue;
}
model = new Manage_Type
{
ApproverAccount = ApproverAccount[workBook.GetSheetIndex(sheet)],
TypeName = cell.StringCellValue,
ParentId = arrTemp[m - 1].Id
};
if (isNewRow)
{
arrTemp[m] = model;
}
listModel.Add(model);
}
}
}
return(listModel);
}
/// <summary>
/// 将excel中的多个SHEET的数据导入到DataSet中
/// </summary>
/// <param name="Content">Stream</param>
/// <param name="sheetNames">多个sheet名称</param>
/// <returns>返回的DataSet</returns>
public DataSet GetExcelToSet07(Stream Content, string[] sheetNames) //2007 Excel
{
XSSFWorkbook workbook = new XSSFWorkbook(Content);
DataSet dataset = new DataSet();
foreach (string sheetname in sheetNames)
{
int index = workbook.GetSheetIndex(sheetname);
if (index == -1)
{
return(null);
}
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(sheetname);
DataTable table = new DataTable(sheetname);
//获取sheet的首行
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum + 1;
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
if (row == null || row.Cells.Count == 0)
{
continue;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
//dataRow[j] = row.GetCell(j).ToString();
if (row.GetCell(j).CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy年MM月");
}
else//其他数字类型
{
if (!string.IsNullOrEmpty(row.GetCell(j).CellStyle.GetDataFormatString()) && row.GetCell(j).CellStyle.GetDataFormatString().Contains("%"))
{
dataRow[j] = row.GetCell(j).NumericCellValue * 100;
}
else
{
dataRow[j] = row.GetCell(j).NumericCellValue;
}
}
}
else if (row.GetCell(j).CellType == CellType.Blank)//空数据类型
{
dataRow[j] = "";
}
else if (row.GetCell(j).CellType == CellType.Formula)//公式类型
{
XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
dataRow[j] = eva.Evaluate(row.GetCell(j)).StringValue;
}
else //其他类型都按字符串类型来处理
{
dataRow[j] = row.GetCell(j).StringCellValue;
}
}
}
table.Rows.Add(dataRow);
}
dataset.Tables.Add(table);
}
return(dataset);
}
/// <summary>
/// 读取excel2007
/// 当string.IsNullOrEmpty(sheetName)为TRUE时，则按sheetIndex读取
/// 否则，按sheetName读取
/// </summary>
/// <param name="Content"></param>
/// <returns></returns>
public DataTable GetExcelToTable07(Stream Content, string sheetName, int sheetIndex = 0) //2007 Excel
{
XSSFWorkbook workbook = new XSSFWorkbook(Content);
//获取excel的第一个sheet
XSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = (XSSFSheet)workbook.GetSheetAt(sheetIndex);
}
else
{
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return(null);
}
sheet = (XSSFSheet)workbook.GetSheet(sheetName);
}
sheet.ForceFormulaRecalculation = true;
DataTable table = new DataTable();
/*try
* {*/
//获取sheet的首行
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
ICell cell = null;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
cell = headerRow.GetCell(i);
DataColumn column;
if (headerRow.GetCell(i).CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
column = new DataColumn(cell.DateCellValue.ToString("yyyy年MM月"));
}
else//其他数字类型
{
column = new DataColumn(cell.NumericCellValue.ToString());
}
}
else if (cell.CellType == CellType.Blank)//空数据类型
{
column = new DataColumn("");
}
else //其他类型都按字符串类型来处理
{
column = new DataColumn(cell.StringCellValue);
}
cell = null;
table.Columns.Add(column);
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum + 1;
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
if (row == null || row.Cells.Count == 0)
{
continue;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
cell = row.GetCell(j);
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
if (cell.CellStyle.GetDataFormatString().Contains("日") || cell.CellStyle.GetDataFormatString().Contains("d"))
{
dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月dd日");
}
else
{
dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月");
}
}
else//其他数字类型
{
if (!string.IsNullOrEmpty(cell.CellStyle.GetDataFormatString()) && cell.CellStyle.GetDataFormatString().Contains("%"))
{
dataRow[j] = cell.NumericCellValue * 100;
}
else if (cell.CellStyle.DataFormat.GetHashCode() == 3735609)
{
dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月");
}
else if (cell.CellStyle.DataFormat.GetHashCode() == 2031647)
{
dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月dd日");
}
else
{
dataRow[j] = cell.NumericCellValue;
}
// 处理科学计数
if (dataRow[j].ToString().Contains("E"))
{
Decimal dData = 0.0M;
dData = Convert.ToDecimal(Decimal.Parse(dataRow[j].ToString(), System.Globalization.NumberStyles.Float));
dataRow[j] = dData;
//FileLog.WriteLog("科学计数：" + dataRow[j].ToString());
}
}
}
else if (cell.CellType == CellType.Blank)//空数据类型
{
dataRow[j] = "";
}
else if (cell.CellType == CellType.Formula)//公式类型
{
XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
CellValue cv = eva.Evaluate(cell);
switch (cv.CellType)
{
case CellType.Numeric:
dataRow[j] = cv.NumberValue;
break;
case CellType.String:
dataRow[j] = cv.StringValue;
break;
case CellType.Error:
dataRow[j] = cv.ErrorValue;
break;
}
//FileLog.WriteLog("公式:" + dataRow[j]);
}
else //其他类型都按字符串类型来处理
{
cell.SetCellType(CellType.String);
dataRow[j] = cell.StringCellValue;
}
}
cell = null;
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
/*}
* catch (Exception ex)
* {
* FileLog.WriteLog("Exception: " + ex);
* table = null;
* }*/
return(table);
}
public static void GenerateDmgWorkbook(List <AtkData> atkDatas, string name)
{
string sheetName = $"{name}_{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}";
XSSFWorkbook dmgWorkbook = IOUtils.OpenOrCreateDmgWorkbook();
if (dmgWorkbook.GetSheetIndex(sheetName) != -1)
{
ConsoleLog.Error("Excel生成", "今天已进行过统计，删除旧表");
dmgWorkbook.RemoveSheetAt(dmgWorkbook.GetSheetIndex(sheetName));
}
ISheet dmgSheet = dmgWorkbook.CreateSheet(sheetName); //新建一个工作表
dmgSheet.CreateRow(0);
//写入第一行数据
IRow firstRow = dmgSheet.GetRow(0);
int rowCount = 0;
firstRow.CreateCell(rowCount++).SetCellValue("QQ");
firstRow.CreateCell(rowCount++).SetCellValue("昵称");
firstRow.CreateCell(rowCount++).SetCellValue("总伤害");
firstRow.CreateCell(rowCount++).SetCellValue("平均伤害");
firstRow.CreateCell(rowCount++).SetCellValue("出刀次数");
firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)");
firstRow.CreateCell(rowCount++).SetCellValue("对一王总伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对一王平均伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对一王出刀次数");
firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)");
firstRow.CreateCell(rowCount++).SetCellValue("对二王总伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对二王平均伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对二王出刀次数");
firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)");
firstRow.CreateCell(rowCount++).SetCellValue("对三王总伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对三王平均伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对三王出刀次数");
firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)");
firstRow.CreateCell(rowCount++).SetCellValue("对四王总伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对四王平均伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对四王出刀次数");
firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)");
firstRow.CreateCell(rowCount++).SetCellValue("对五王总伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对五王平均伤害");
firstRow.CreateCell(rowCount++).SetCellValue("对五王出刀次数");
firstRow.CreateCell(rowCount).SetCellValue("RSD(%)");
//写入伤害数据
int rowNum = 1;
foreach (AtkData atkData in atkDatas)
{
int colNum = 0;
dmgSheet.CreateRow(rowNum); //创建行
IRow sheetRow = dmgSheet.GetRow(rowNum); // 获得行索引
//总伤害数据
sheetRow.CreateCell(colNum++).SetCellValue(atkData.Uid.ToString());
sheetRow.CreateCell(colNum++).SetCellValue(atkData.Name);
sheetRow.CreateCell(colNum++).SetCellValue(atkData.TotalDmg);
sheetRow.CreateCell(colNum++).SetCellValue(double.IsNaN(atkData.TotalAvgDmg) ? 0 : atkData.TotalAvgDmg);
sheetRow.CreateCell(colNum++).SetCellValue(atkData.TotalTimes);
sheetRow.CreateCell(colNum++).SetCellValue(atkData.Deviation);
//boss伤害数据
foreach (BossDmg bossDmg in atkData.BossDmgInfos)
{
sheetRow.CreateCell(colNum++).SetCellValue(bossDmg.Dmg);
sheetRow.CreateCell(colNum++).SetCellValue(double.IsNaN(bossDmg.AvgDmg) ? 0 : bossDmg.AvgDmg);
sheetRow.CreateCell(colNum++).SetCellValue(bossDmg.Count);
sheetRow.CreateCell(colNum++).SetCellValue(bossDmg.Deviation);
}
rowNum++;
}
//设置自动宽度
for (int i = 0; i < 20; i++)
{
dmgSheet.AutoSizeColumn(i);
}
//写入数据到文件
IOUtils.WriteToWorkbookFile(dmgWorkbook);
}
public override void UpdateConditionalFormatting(FormulaShifter Shifter)
{
XSSFSheet xsheet = (XSSFSheet)sheet;
XSSFWorkbook wb = xsheet.Workbook as XSSFWorkbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
CT_Worksheet ctWorksheet = xsheet.GetCTWorksheet();
List <CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting;
// iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--)
{
CT_ConditionalFormatting cf = conditionalFormattingArray[j];
List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
String[] regions = cf.sqref.ToString().Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
cellRanges.Add(CellRangeAddress.ValueOf(regions[i]));
}
bool Changed = false;
List <CellRangeAddress> temp = new List <CellRangeAddress>();
for (int i = 0; i < cellRanges.Count; i++)
{
CellRangeAddress craOld = cellRanges[i];
CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex);
if (craNew == null)
{
Changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
Changed = true;
}
}
if (Changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
conditionalFormattingArray.RemoveAt(j);
continue;
}
string refs = string.Empty;
foreach (CellRangeAddress a in temp)
{
if (refs.Length == 0)
{
refs = a.FormatAsString();
}
else
{
refs += " " + a.FormatAsString();
}
}
cf.sqref = refs;
}
foreach (CT_CfRule cfRule in cf.cfRule)
{
List <String> formulas = cfRule.formula;
for (int i = 0; i < formulas.Count; i++)
{
String formula = formulas[i];
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, -1);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}
public override void RemoveSheet(string name)
{
int index = workbook.GetSheetIndex(name);
workbook.RemoveAt(index);
}
public virtual void PassDataToExcel(ISmartTool tool)
{
switch (tool.Type)
{
case SmartToolType.Telescope:
TemplateFileName = "TeleScope Diagram.xlsx";
break;
case SmartToolType.Gdis:
TemplateFileName = "GDIS Diagram.xlsx";
break;
}
var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}";
try
{
using (
var file =
new FileStream(filePath,
FileMode.Open, FileAccess.Read))
{
Book = new XSSFWorkbook(file);
}
Sheet = Book.GetSheetAt(0);
Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{tool.Top.Name}_{tool.Top.SerialNumber}");
//Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0)
//Поэтому от номера строки и столбца нужно отнимать 1
var cellNum = 2;
//MDC SerialNumber
SetCellValue(7, cellNum, tool.Middle.SerialNumber);
//Top Sub SerialNumber
SetCellValue(9, cellNum, tool.Top.SerialNumber);
//Bottom Sub SerialNumber
SetCellValue(11, cellNum, tool.Bottom.SerialNumber);
cellNum = 4;
//Top Sub OD
SetCellValue(14, cellNum, tool.Top.ConnectionOne.Od);
//MDC OD
SetCellValue(20, cellNum, tool.Middle.ConnectionOne.Od);
//Bottom Sub OD
SetCellValue(64, cellNum, tool.Bottom.ConnectionOne.Od);
//Bottom Sub ID
SetCellValue(75, 7, tool.Bottom.ConnectionTwo.Id);
cellNum = 9;
//Top Sub Treadsize
SetCellValue(7, cellNum, tool.Top.ConnectionOne.TreadSize);
//Bottom Sub Treadsize
SetCellValue(75, cellNum, tool.Bottom.ConnectionTwo.TreadSize);
cellNum = 10;
//L
var lMdc = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Middle.Length));
var lBotSub = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Bottom.Length));
var lTopSub = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Top.Length));
//L MDC + Bottom Sub
SetCellValue(17, cellNum, (lMdc + lBotSub).ToString("0.000"));
//L MDC + Bottom Sub
SetCellValue(61, cellNum, lBotSub.ToString("0.000"));
//L MDC + Bottom Sub + Top Sub
SetCellValue(40, 13, (lMdc + lBotSub + lTopSub).ToString("0.000"));
string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{
tool.Top.Name}_{tool.Top.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx";
//Сохранение изменённого файла
using (
var file =
new FileStream(fileName, FileMode.CreateNew, FileAccess.Write))
{
Book.Write(file);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "I have a bad feeling about this", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
}
public override void PassDataToExcel(IParsedData data)
{
var crossoverData = (CrossoverSubParsedData)data;
var crossoverType = crossoverData.Type;
switch (crossoverType)
{
case CrossoverType.Type1:
TemplateFileName = "Crossover Sub Type 1 Diagram.xlsx";
break;
case CrossoverType.Type2:
TemplateFileName = "Crossover Sub Type 2 Diagram.xlsx";
break;
case CrossoverType.Type3:
TemplateFileName = "Crossover Sub Type 3 Diagram.xlsx";
break;
case CrossoverType.Type4:
TemplateFileName = "Crossover Sub Type 4 Diagram.xlsx";
break;
case CrossoverType.NotDefined:
MessageBox.Show("Crossover Sub Type Not Defined", "Information message", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
return;
}
if (string.IsNullOrEmpty(TemplateFileName))
{
return;
}
var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}";
try
{
using (
var file =
new FileStream(filePath,
FileMode.Open, FileAccess.Read))
{
_xlsBook = new XSSFWorkbook(file);
}
_xlsBook.SetForceFormulaRecalculation(true);
Sheet = _xlsBook.GetSheetAt(0);
Sheet = _xlsBook.GetSheetAt(0);
_xlsBook.SetSheetName(_xlsBook.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}");
//Запись заголовка
FillHeader(crossoverData);
//cellNum - Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные
var cellNum = 5;
//SerialNumber
SetCellValue(14, cellNum, crossoverData.SerialNumber);
//TOP
SetCellValue(16, cellNum, crossoverData.ConnectionOne.TreadSize);
//BOT
SetCellValue(17, cellNum, crossoverData.ConnectionTwo.TreadSize);
//L
var inches = InchesValueRetriever.GetInchesValue(crossoverData.Length);
SetCellValue(19, cellNum,
inches.Equals(0) ? string.Empty : LengthConverter.InchesToMeters(inches).ToString("0.000"));
switch (crossoverType)
{
case CrossoverType.Type1:
//OD
SetCellValue(20, cellNum, crossoverData.ConnectionOne.Od);
//ID
SetCellValue(21, cellNum, crossoverData.ConnectionTwo.Id);
break;
case CrossoverType.Type2:
//ID1
SetCellValue(20, cellNum, crossoverData.ConnectionOne.Id);
//ID2
SetCellValue(21, cellNum, crossoverData.ConnectionTwo.Id);
break;
case CrossoverType.Type3:
case CrossoverType.Type4:
//FishingNeck
SetCellValue(20, cellNum, crossoverData.FishingNeck);
//Od1
SetCellValue(21, cellNum, crossoverData.ConnectionOne.Od);
//ID2
SetCellValue(23, cellNum, crossoverData.ConnectionTwo.Id);
//Od2
SetCellValue(24, cellNum, crossoverData.ConnectionTwo.Od);
break;
}
string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{
crossoverData.Name}_{crossoverData.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-s")}.xlsx";
//Сохранение изменённого файла
using (
var file =
new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
_xlsBook.Write(file);
}
}
catch (Exception e)
{
MessageBox.Show($"Something is wrong: {e.Message}", "Viva La Resistance!!!", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
}
public override void PassDataToExcel(IParsedData data)
{
var stabilizerData = (IStabilizerParsedData)data;
//... (прдолжение) чтобы использовать поля из StabilizerParsedData, необходимо вот так вот
//как здесь выполнить приведение к призводному типу. Просто небольшой нюанс, если вы будете делать свои версии класса ParsedData
if (string.IsNullOrEmpty(TemplateFileName))
{
return;
}
var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}";
try
{
using (
var file =
new FileStream(filePath,
FileMode.Open, FileAccess.Read))
{
_xlsBook = new XSSFWorkbook(file);
}
_xlsBook.SetForceFormulaRecalculation(true);
Sheet = _xlsBook.GetSheetAt(0);
Sheet = _xlsBook.GetSheetAt(0);
_xlsBook.SetSheetName(_xlsBook.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}");
//Запись заголовка
FillHeader(stabilizerData);
//cellNum - Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные
var cellNum = 6;
//SerialNumber
SetCellValue(14, cellNum, stabilizerData.SerialNumber);
//TOP
SetCellValue(17, cellNum, stabilizerData.ConnectionOne.TreadSize);
//BOT
SetCellValue(18, cellNum, stabilizerData.ConnectionTwo.TreadSize);
//L
var inches = InchesValueRetriever.GetInchesValue(stabilizerData.Length);
SetCellValue(22, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000"));
//L1
inches = InchesValueRetriever.GetInchesValue(stabilizerData.FishingNeckTongSpace);
SetCellValue(23, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000"));
//OD
SetCellValue(29, cellNum, stabilizerData.ConnectionOne.Od);
//ID
SetCellValue(30, cellNum, stabilizerData.ConnectionTwo.Od);
//MaxOD
SetCellValue(31, cellNum, stabilizerData.StabilizerOd);
//BladeLength
inches = InchesValueRetriever.GetInchesValue(stabilizerData.LobeLength);
SetCellValue(32, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000"));
//BladeWidth
SetCellValue(34, cellNum, stabilizerData.LobeWidth);
string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{
stabilizerData.Name}_{stabilizerData.SerialNumber}_FishingDiagram_{
DateTime.Now.ToString("yy-MM-dd-HH-mm-s")}.xlsx";
//Сохранение изменённого файла
using (
var file =
new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
_xlsBook.Write(file);
}
}
catch (Exception e)
{
MessageBox.Show($"Something is wrong: {e.Message}", "Viva La Resistance!!!", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
}
public override void PassDataToExcel(IParsedData data)
{
if (string.IsNullOrEmpty(TemplateFileName))
{
return;
}
var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}";
try
{
using (
var file =
new FileStream(filePath,
FileMode.Open, FileAccess.Read))
{
Book = new XSSFWorkbook(file);
}
Sheet = Book.GetSheetAt(0);
Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}");
//Заполнение заголовка (метод прописан в ExcelProcessor.cs)
FillHeader(data);
//Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0)
//Поэтому от номера строки и столбца нужно отнимать 1
var cellNum = 5;
//SerialNumber
SetCellValue(14, cellNum, data.SerialNumber); //соответствует 22 строке в шаблоне и т.д.
//L
var inches = InchesValueRetriever.GetInchesValue(data.Length);
SetCellValue(19, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000"));
//Od1
SetCellValue(20, cellNum, data.ConnectionOne.Od);
//ID
SetCellValue(21, cellNum, data.ConnectionTwo.Od);
//Top
SetCellValue(16, cellNum, data.ConnectionOne.TreadSize);
//Bottom
SetCellValue(17, cellNum, data.ConnectionTwo.TreadSize); //соответствует 27 строке в шаблоне
//var totalFishingDiagram =
// TotalFishingDiagramFile.GetTotalFishingDiagramFileStream(
// $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\out\Field_Pad_Well_FishingDiagram_{
// SessionStartTime}.xlsx");
//var totalBook = new XSSFWorkbook(totalFishingDiagram);
string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{
data.Name}_{data.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx";
//Сохранение изменённого файла
using (
var file =
new FileStream(fileName, FileMode.CreateNew, FileAccess.Write))
{
Book.Write(file);
}
}
catch (Exception e)
{
MessageBox.Show($"I have a bad feeling about this: {e.Message}", "Viva La Resistance!!!", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
}
public int GetExternalSheetIndex(String sheetName)
{
int sheetIndex = _uBook.GetSheetIndex(sheetName);
return(ConvertToExternalSheetIndex(sheetIndex));
}
public void GenerateDocument(List <string> tableNames, string templatePath, string outputDocPath)
{
const string sheetName4TableListTempalte = "#TableListTemplate";
const string sheetName4TableSchemaTempalte = "#TableSchemaTemplate";
const string tagName4TableNo = "#table.no";
const string tagName4TableName = "#table.name";
const string tagName4TableDesc = "#table.description";
const string tagName4TableView = "#table.view";
const string tagName4TableType = "#table.type";
const string tagName4ColumnNo = "#column.no";
const string tagName4ColumnName = "#column.name";
const string tagName4ColumnPK = "#column.pk";
const string tagName4ColumnFK = "#column.fk";
const string tagName4ColumnFKReference = "#column.fkreference";
const string tagName4ColumnNullable = "#column.nullable";
const string tagName4ColumnIdentity = "#column.identity";
const string tagName4ColumnDataType = "#column.datatype";
const string tagName4ColumnDefault = "#column.default";
const string tagName4ColumnDesc = "#column.description";
// open template file
XSSFWorkbook workbook;
using (FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fs);
}
// TABLE LIST
// clone tempalte table list sheet & rename sheet
var tableSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableListTempalte));
workbook.SetSheetName(workbook.NumberOfSheets - 1, "Table List");
var tableIndex = 1;
foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName)))
{
var noLocation = tableSheet.FindCellLocation(tagName4TableNo);
if (noLocation.HasValue)
{
// copy the table info row from tempalte
tableSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1);
var newRowIndex = noLocation.Value.Y;
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableNo, tableIndex.ToString());
tableSheet.SetFirstMatchCellHyperlinkInRow(newRowIndex, tagName4TableName, table.TableName, tableIndex.ToString());
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableDesc, table.Description);
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableView, table.IsViewTable ? "V" : "");
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableType, table.TableType.ToTitleCase());
tableIndex++;
}
}
// remove the last template row
tableSheet.RemoveFirstMatchRow(tagName4TableNo);
// TABLE SCHEMA SHEETS
tableIndex = 1;
foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName)))
{
// clone tempalte schema sheet & rename sheet
var schemaSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableSchemaTempalte));
workbook.SetSheetName(workbook.NumberOfSheets - 1, tableIndex.ToString() /*table.TableName*/);
// table name & description
schemaSheet.SetFirstMatchCellContent(tagName4TableName, table.TableName);
schemaSheet.SetFirstMatchCellContent(tagName4TableDesc, table.Description);
schemaSheet.SetFirstMatchCellContent(tagName4TableView, table.IsViewTable ? "V" : "");
schemaSheet.SetFirstMatchCellContent(tagName4TableType, table.TableType.ToTitleCase());
// prepare each column info
foreach (var column in table.Columns)
{
var noLocation = schemaSheet.FindCellLocation(tagName4ColumnNo);
if (noLocation.HasValue)
{
// copy the column info row from tempalte
schemaSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1);
var newRowIndex = noLocation.Value.Y;
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNo, column.No.ToString());
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnName, column.ColumnName);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnPK, column.IsPrimaryKey ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFK, column.IsForeignKey ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFKReference, column.FkReferencedInfo);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNullable, column.IsNullable ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnIdentity, column.IsIdentity ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDataType, column.FullDataType);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDefault, column.Default);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDesc, column.Description);
}
}
// remove the last template row
schemaSheet.RemoveFirstMatchRow(tagName4ColumnNo);
tableIndex++;
}
// remove template sheet
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableListTempalte));
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableSchemaTempalte));
// save as another excel file
using (FileStream stream = new FileStream(outputDocPath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
// set null
workbook = null;
}
/// <summary>
///從datatable中導出到excel
/// </summary>
/// <param name="dtSource"> datatable數據源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="fs">文件流</param>
/// <param name="readfs">內存流</param>
/// <param name="sheetnum"> sheet索引</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary <string, string> dir, int sheetnum)
{
IWorkbook workbook = new XSSFWorkbook();
if (readfs.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(readfs);
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(" yyyy-mm-dd ");
//取得列寬
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列首，樣式
if (rowIndex == 0)
{
#region 表頭及樣式
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列首及樣式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充內容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case " System.String ": //字符串類型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case " System.DateTime ": //日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化顯示
break;
case " System.Boolean ": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case " System.Int16 ": //整型
case " System.Int32 ":
case " System.Int64 ":
case " System.Byte ":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case " System.Decimal ": //浮點型
case " System.Double ":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case " System.DBNull ": //空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
// 上传数据模板
// POST api/inputapi/upload
public Object upload()
{
System.Web.HttpFileCollection _file = System.Web.HttpContext.Current.Request.Files;
int count = 0;
if (_file.Count > 0)
{
for (int i = 0; i < _file.Count; i++)
{
//文件大小
//long size = _file[i].ContentLength;
//文件类型
//string type = _file[i].ContentType;
//文件名
string _filename = _file[i].FileName;
//文件格式
string _tp = System.IO.Path.GetExtension(_filename);
if (_tp.ToLower() == ".xlsx")
{
BaseService bs = new BaseService();
string userid = UtilFunction.GetCookie("uid");
string username = UtilFunction.GetCookie("uname");
string _directory2 = "UploadFiles/" + username;
string path = _directory + _directory2;
string key = null;
List <Object> parames = new List <Object>();
Dictionary <string, List <Object> > map = new Dictionary <string, List <Object> >();
//获取文件流
//System.IO.Stream stream = _file[i].InputStream;
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path += "/" + _filename;
if (File.Exists(path)) // 如果当前用户下已有同名文件，则执行update操作
{
key = "updateFileInfo";
parames.Add(DateTime.Now);
parames.Add(userid);
parames.Add(username);
parames.Add(_filename);
parames.Add(userid);
}
else // insert
{
key = "insertFileInfo";
parames.Add(_filename);
parames.Add(_directory2);
parames.Add(DateTime.Now);
parames.Add(userid);
parames.Add(username);
}
map.Add(key, parames);
// 上传文件
_file[i].SaveAs(path);
XSSFWorkbook workbook = new XSSFWorkbook(_file[i].InputStream);
int index = workbook.GetSheetIndex("填写模板");
if (index > -1)
{
// 上传记录入库
count = bs.InsUpdDelDataTableToParam(map);
}
else
{
File.Delete(path);
return(new { error = "《" + _filename + "》文件中必须有一个sheet名叫“填写模板”!" });
}
}
else
{
return(new { error = "《" + _filename + "》只允许上传后缀为.xlsx的文件!" });
}
}
}
if (count > 0)
{
return(new { msg = "数据模板上传成功!" });
}
else
{
return(new { error = "数据模板上传失败!" });
}
}
public override void PassDataToExcel(IParsedData data)
{
if (string.IsNullOrEmpty(TemplateFileName))
{
return;
}
var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}";
try
{
using (
var file =
new FileStream(filePath,
FileMode.Open, FileAccess.Read))
{
Book = new XSSFWorkbook(file);
}
Sheet = Book.GetSheetAt(0);
Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}");
var nmdcData = new NmdcFData();
if (!nmdcData.Tools.ContainsKey(data.SerialNumber))
{
MessageBox.Show("No such NMDC-F in library", "Information", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
return;
}
var nmdcTool = nmdcData.Tools[data.SerialNumber];
var inspectionLength = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(data.Length));
var arrayLength = Convert.ToSingle(nmdcTool.L, CultureInfo.InvariantCulture);
var difference = Math.Abs(inspectionLength - arrayLength);
if (difference > 0.025f)
{
MessageBox.Show($"Collar length {inspectionLength} doesn't match. Should be about {arrayLength}. Difference is {difference}. Prepare fishing diagram manually.", "Information", MessageBoxButton.OK,
MessageBoxImage.Asterisk);
return;
}
//Заполнение заголовка (сам метод внизу)
FillHeader(data);
//Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0)
//Поэтому от номера строки и столбца нужно отнимать 1
var cellNum = 4;
//L
SetCellValue(12, cellNum, inspectionLength.ToString("0.000"));
//L12
SetCellValue(15, cellNum, nmdcTool.L12);
//L11
SetCellValue(16, cellNum, nmdcTool.L11);
//L10
SetCellValue(20, cellNum, nmdcTool.L10);
//L9
SetCellValue(21, cellNum, nmdcTool.L9);
//L8
SetCellValue(24, cellNum, nmdcTool.L8);
//L7
SetCellValue(25, cellNum, nmdcTool.L7);
//L6
SetCellValue(29, cellNum, nmdcTool.L6);
//L5
SetCellValue(30, cellNum, nmdcTool.L5);
//L4
SetCellValue(33, cellNum, nmdcTool.L4);
//L3
SetCellValue(34, cellNum, nmdcTool.L3);
//L2
SetCellValue(38, cellNum, nmdcTool.L2);
//L1
SetCellValue(39, cellNum, nmdcTool.L1);
cellNum = 8;
//SerialNumber
SetCellValue(12, cellNum, data.SerialNumber); //соответствует 22 строке в шаблоне и т.д.
//OD
SetCellValue(13, cellNum, data.ConnectionOne.Od);
//ID
SetCellValue(14, cellNum, data.ConnectionTwo.Id);
//Od6
SetCellValue(19, cellNum, nmdcTool.Od6);
//Od5
SetCellValue(20, cellNum, nmdcTool.Od5);
//Od4
SetCellValue(21, cellNum, nmdcTool.Od4);
//Od3
SetCellValue(22, cellNum, nmdcTool.Od3);
//Od2
SetCellValue(23, cellNum, nmdcTool.Od2);
//Od1
SetCellValue(24, cellNum, nmdcTool.Od1);
string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{
data.Name}_{data.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx";
//Сохранение изменённого файла
using (
var file =
new FileStream(fileName, FileMode.CreateNew, FileAccess.Write))
{
Book.Write(file);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "I have a bad feeling about this", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
}
