/// <summary>
/// 判断xlsx和xls的Excel中的sheet是否存在空行（整行数据都是空值）
/// </summary>
/// <param name="xlsRow">xls Excel行对象实体</param>
/// <param name="xlsxRow">xlsx Excel行对象实体</param>
/// <returns></returns>
public static bool IsEmptyRow(HSSFRow xlsRow, XSSFRow xlsxRow)
{
try
{
IRow row = xlsRow ?? (IRow)xlsxRow;
if (row != null)
{
var emptyCellCount = 0;
for (int i = 0; i <= row.Cells.Count - 1; i++)
{
if (row.Cells[i] != null)
{
if (string.IsNullOrEmpty(row.Cells[i].ToString()))
{
++emptyCellCount;
}
}
else
{
++emptyCellCount;
}
}
// return emptyCellCount == row.Cells.Count - 1;
return(emptyCellCount == row.Cells.Count);
}
return(true);
}
catch (Exception ex)
{
LogUtil.WriteException(ex);
}
return(false);
}
private void renderLastTestSheet()
{
XSSFSheet totalST = newwb.CreateSheet("最后一次测试") as XSSFSheet;
XSSFRow newRow = totalST.CreateRow(0) as XSSFRow;
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue("老师");
newRow.CreateCell(1).SetCellValue("手机号");
newRow.CreateCell(3).SetCellValue("课程名");
newRow.CreateCell(4).SetCellValue("测试名");
newRow.CreateCell(2).SetCellValue("差值");
for (var j = 0; j < dtTotal.Rows.Count; j++)
{
newRow = totalST.CreateRow(j + 1) as XSSFRow;
string teacher = dtTotal.Rows[j]["teacher"].ToString();
string mobile = dtTotal.Rows[j]["mobile"].ToString();
string dvalue = dtTotal.Rows[j]["dvalue"].ToString();
string className = dtTotal.Rows[j]["className"].ToString();
string testName = dtTotal.Rows[j]["testName"].ToString();
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue(teacher);
newRow.CreateCell(1).SetCellValue(mobile);
newRow.CreateCell(2).SetCellValue(dvalue);
newRow.CreateCell(3).SetCellValue(className);
newRow.CreateCell(4).SetCellValue(testName);
}
}
public static void ExportDataTableToExcel(DataTable dtSource, string strFileName)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("mySheet");
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
// create the cells in the first row, and add data into these cells circularly
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//create rows on the basis of data from datatable(not including table header), and add data into cells in every row
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
/// <summary>
/// datatable数据转excel多Sheet(单行表头)
/// </summary>
/// <returns></returns>
private void DataTableToExcelBySingleHead(ISheet xSheet, DataTable dt)
{
try
{
XSSFRow headRow = (XSSFRow)xSheet.CreateRow(0);
foreach (DataColumn dc in dt.Columns)
{
headRow.CreateCell(dc.Ordinal).SetCellValue(dc.ColumnName.ToString().Trim());
SetCellStyle(headRow.Cells[dc.Ordinal], "title");
}
int rowIndex = 1;
foreach (DataRow dr in dt.Rows)
{
XSSFRow xssfRow = (XSSFRow)xSheet.CreateRow(rowIndex);
foreach (DataColumn dc in dt.Columns)
{
xssfRow.CreateCell(dc.Ordinal).SetCellValue(dr[dc].ToString().Trim());
SetCellStyle(xssfRow.Cells[dc.Ordinal], "");
}
rowIndex += 1;
}
//列宽自适应,只对英文和数字有效
for (int i = 0; i < dt.Columns.Count; i++)
{
xSheet.SetColumnWidth(i, 15 * 256);
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Button7_Click(object sender, EventArgs e) //匯入
{
if (FileUpload1.HasFile)
{
try
{
//XSSFWorkbook 活頁簿
XSSFWorkbook myWorkbook = new XSSFWorkbook(FileUpload1.FileContent);
//建立XSSFSHEET 工作表
ISheet mySheet = myWorkbook.GetSheetAt(0);
//建立DATATABLE
DataTable myDT = new DataTable();
//抓取MYSHEET工作表中的標題欄位，並存入DATATABLE
XSSFRow headerRow = mySheet.GetRow(0) as XSSFRow;
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum - 2; i++)
{
if (headerRow.GetCell(i) != null)
{
DataColumn myColumn = new DataColumn(headerRow.GetCell(i).StringCellValue);
myDT.Columns.Add(myColumn);
}
}
//抓取XSSFSHEET第一列以後的所有資料，並存入DATATABLE中
for (int i = mySheet.FirstRowNum + 1; i <= mySheet.LastRowNum; i++)
{
XSSFRow row = mySheet.GetRow(i) as XSSFRow;
DataRow myRow = myDT.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum - 2; j++)
{
if (row.GetCell(j) != null)
{
myRow[j] = row.GetCell(j).ToString();
}
}
myDT.Rows.Add(myRow);
}
//釋放活頁簿、工作表資源
myWorkbook = null;
mySheet = null;
DataView myView = new DataView(myDT);
GridView2.DataSource = myDT;
GridView2.DataBind();
Label9.Text = "上傳成功";
}
catch (Exception ex)
{
Response.Write("thie Error Message---" + ex.ToString());
}
}
else
{
Label9.Text = "請先挑選檔案之後";
}
}
public static void CreateCell(XSSFRow row, int cellIndex, double cellValue, ICellStyle style)
{
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(cellValue);
cell.CellStyle = style;
}
public void ExcelImportStart(DataTable datable, string sheetname, int rowint)
{
XSSFSheet ws = (XSSFSheet)hssfworkbook.GetSheet(sheetname);
//添加或修改WorkSheet里的数据
System.Data.DataTable dt = datable;
#region
if (dt.Rows.Count > 0)
{
var rowIndex = rowint;
foreach (DataRow row in dt.Rows)
{
XSSFRow dataRow = ws.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dt.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
newCell.SetCellValue(drValue);
}
rowIndex++;
}
}
#endregion
ws.ForceFormulaRecalculation = true;
}
//处理excel2007
private static DataTable ExcelToDataTable(XSSFSheet sheet, XSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != 0)
{
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
dt.Columns.Add(string.Format("F{0}", i), typeof(string));
}
for (int i = 0; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
}
dt.TableName = sheet.SheetName;
return(dt);
}
}
public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
public void exportVariety(string varietyName, int agreement, DateTime startingDate, DateTime endingDate)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1");
setColumnWidth(sheet);
initStyle(workbook);
//第一行，品种、起止时间
XSSFRow row0 = (XSSFRow)sheet.CreateRow(0);
setRowHeight(row0);
XSSFCell cell = (XSSFCell)row0.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(varietyName + Variety.getAgreementName(agreement));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
cell = (XSSFCell)row0.CreateCell(2);
cell.CellStyle = headerStyle;
cell = (XSSFCell)row0.CreateCell(1);
cell.CellStyle = headerStyle;
cell.SetCellValue(Utils.formatDate(startingDate) + "-" + Utils.formatDate(endingDate));
excelFilePath = Utils.getExportDir() + varietyName + "_" + Variety.getAgreementName(agreement) + "_" + Utils.getDate() + "_回测" + "_" + Utils.getTimeMillisecond() + ".xlsx";
FileStream file = new FileStream(excelFilePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
public static void SaveRToXls(Stream filegrp, string fileidx, string inifilepath, string xlsFile)
{
XSSFWorkbook newBook = new XSSFWorkbook();
List <object> rows = new List <object>();
List <RdataType> datalist = ReadRanger(filegrp, fileidx, inifilepath);
for (int k = 0; k < datalist.Count; k++)
{
object[,] array = datalist[k].ToArray();
XSSFSheet newSheet = (XSSFSheet)newBook.CreateSheet(datalist[k].TypeName);//新建工作簿
for (int i = 0; i < array.GetLength(1); i++)
{
XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i);//创建行
for (int j = 0; j < array.GetLength(0); j++)
{
if (array[j, i].GetType() == typeof(String))
{
newSheet.GetRow(i).CreateCell(j).SetCellValue((string)array[j, i]);
}
else
{
newSheet.GetRow(i).CreateCell(j).SetCellValue((int)array[j, i]);
}
}
}
//newSheet.AutoSizeColumn(-2);
}
FileStream fs = new FileStream(xlsFile, FileMode.Create);
newBook.Write(fs);
fs.Close();
fs.Dispose();
}
public void Bug56274()
{
// read sample file
XSSFWorkbook inputWorkbook = XSSFTestDataSamples.OpenSampleWorkbook("56274.xlsx");
// read the original sheet header order
XSSFRow row = inputWorkbook.GetSheetAt(0).GetRow(0) as XSSFRow;
List <String> headers = new List <String>();
foreach (ICell cell in row)
{
headers.Add(cell.StringCellValue);
}
// no SXSSF class
// save the worksheet as-is using SXSSF
//File outputFile = File.CreateTempFile("poi-56274", ".xlsx");
//SXSSFWorkbook outputWorkbook = new NPOI.XSSF.streaming.SXSSFWorkbook(inputWorkbook);
//outputWorkbook.Write(new FileOutputStream(outputFile));
// re-read the saved file and make sure headers in the xml are in the original order
//inputWorkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(new FileStream(outputFile));
inputWorkbook = XSSFTestDataSamples.WriteOutAndReadBack(inputWorkbook) as XSSFWorkbook;
CT_Table ctTable = (inputWorkbook.GetSheetAt(0) as XSSFSheet).GetTables()[0].GetCTTable();
List <CT_TableColumn> ctTableColumnList = ctTable.tableColumns.tableColumn;
Assert.AreEqual(headers.Count, ctTableColumnList.Count,
"number of headers in xml table should match number of header cells in worksheet");
for (int i = 0; i < headers.Count; i++)
{
Assert.AreEqual(headers[i], ctTableColumnList[i].name,
"header name in xml table should match number of header cells in worksheet");
}
//Assert.IsTrue(outputFile.Delete());
}
private void assertCellsWithMissingR(XSSFRow row)
{
XSSFCell a1 = (XSSFCell)row.GetCell(0);
Assert.IsNotNull(a1);
XSSFCell a2 = (XSSFCell)row.GetCell(1);
Assert.IsNotNull(a2);
XSSFCell a5 = (XSSFCell)row.GetCell(4);
Assert.IsNotNull(a5);
XSSFCell a6 = (XSSFCell)row.GetCell(5);
Assert.IsNotNull(a6);
Assert.AreEqual(6, row.LastCellNum);
Assert.AreEqual(4, row.PhysicalNumberOfCells);
Assert.AreEqual(a1.StringCellValue, "A1");
Assert.AreEqual(a2.StringCellValue, "B1");
Assert.AreEqual(a5.StringCellValue, "E1");
Assert.AreEqual(a6.StringCellValue, "F1");
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
Assert.AreEqual(a1.GetReference(), "A1");
Assert.AreEqual(a2.GetReference(), "B1");
Assert.AreEqual(a5.GetReference(), "E1");
Assert.AreEqual(a6.GetReference(), "F1");
}
private void button5_Click(object sender, EventArgs e)
{
if (job.CurrentIndex != job.Count)
{
Comment com = new Comment();
com.ShowDialog();
string txt = com.CommentText;
XSSFWorkbook workbook = ExcelDataTableUtil.LoadExcel(job.JobPath);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(job.CurrentIndex);
row.Cells[dt.Columns.Count - 2].SetCellValue("U");
XSSFColor XlColour = new XSSFColor(button5.BackColor);
XSSFCellStyle cellstyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
row.Cells[dt.Columns.Count - 2].CellStyle = cellstyle;
row.Cells[dt.Columns.Count - 1].SetCellValue(txt);
ExcelDataTableUtil.WriteExcel(workbook, job.JobPath);
job.CurrentIndex++;
job.HasFinished++;
Judge();
}
else
{
MessageBox.Show("已经是最后一条了");
}
}
public void CreateExcelFile <T>(CreateExcelAction <T> createExcelAction, List <T> list, ExcelFormat excelFormat)
{
//建立Excel 2003檔案
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Class");
XSSFRow row = (XSSFRow)ws.CreateRow(0);
row.Height = 440;
ICellStyle positionStyle = wb.CreateCellStyle();
positionStyle.WrapText = true;
positionStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
positionStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
foreach (var item in excelFormat.ColumnFormats)
{
ws.SetColumnWidth(excelFormat.ColumnFormats.IndexOf(item), item.CoiumnWidth);
CreateCell(row, excelFormat.ColumnFormats.IndexOf(item), item.ColumnTitle, positionStyle);
}
int rowIndex = 1;
foreach (var storeData in list)
{
createExcelAction(wb, ws, positionStyle, ref rowIndex, storeData);
}
FileStream file = new FileStream(string.Concat(AppSettingConfig.FilePath(), @"\", excelFormat.FileName, DateTime.Now.ToString("yyyyMMdd"), ".xlsx"), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
void ConvertToDataTable()
{
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
SetData(dt);
}
private static DataTable getDaTableBy07Excel(Stream excelFileStream, int sheetIndex = 0, int headerRowIndex = 0)
{
DataTable table = new DataTable();
XSSFWorkbook workbook = new XSSFWorkbook(excelFileStream);
XSSFSheet sheet = workbook.GetSheetAt(sheetIndex) as XSSFSheet;
XSSFRow headerRow = sheet.GetRow(headerRowIndex) as XSSFRow; //表头行的索引
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = 0; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
if (row == null || row.Cells == null || row.Cells.TrueForAll(p => string.IsNullOrWhiteSpace(p.ToString())))
{
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j) != null?row.GetCell(j) + "" : "";
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return(table);
}
public void WriteData <T>(T obj, Type type, XSSFRow row)
{
if (obj != null)
{
Type t = type;
PropertyInfo[] propertyInfo = t.GetProperties();
int i = 0;
foreach (PropertyInfo propInfo in propertyInfo)
{
object[] objAttrs = propInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true);
if (objAttrs.Length > 0)
{
var cell = row.CreateCell(i);
object value = propInfo.GetValue(obj, null);
if (value != null)
{
if (propInfo.PropertyType == typeof(int))
{
cell.SetCellValue((int)value);
}
else
{
cell.SetCellValue(value.ToString());
}
}
i++;
}
}
}
}
private void CheckWorkbookGrouping(IWorkbook wb, bool?[] collapsed, bool[] hidden, int[] outlineLevel)
{
printWorkbook(wb);
ISheet sheet = wb.GetSheetAt(0);
Assert.AreEqual(collapsed.Length, hidden.Length);
Assert.AreEqual(collapsed.Length, outlineLevel.Length);
Assert.AreEqual(collapsed.Length, sheet.LastRowNum - sheet.FirstRowNum + 1,
"Expected " + collapsed.Length + " rows with collapsed state, but had " + (sheet.LastRowNum - sheet.FirstRowNum + 1) + " rows ("
+ sheet.FirstRowNum + "-" + sheet.LastRowNum + ")");
for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
{
if (collapsed[i - sheet.FirstRowNum] == null)
{
continue;
}
XSSFRow row = (XSSFRow)sheet.GetRow(i);
Assert.IsNotNull(row, "Could not read row " + i);
Assert.IsNotNull(row.GetCTRow(), "Could not read row " + i);
Assert.AreEqual(collapsed[i - sheet.FirstRowNum], row.GetCTRow().collapsed, "Row: " + i + ": collapsed");
Assert.AreEqual(hidden[i - sheet.FirstRowNum], row.GetCTRow().hidden, "Row: " + i + ": hidden");
Assert.AreEqual(outlineLevel[i - sheet.FirstRowNum], row.GetCTRow().outlineLevel, "Row: " + i + ": level");
}
WriteToFile(wb);
}
private void renderTotalSheet()
{
XSSFSheet totalST = newwb.CreateSheet("汇总") as XSSFSheet;
XSSFRow newRow = totalST.CreateRow(0) as XSSFRow;
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue("老师");
newRow.CreateCell(1).SetCellValue("手机号");
newRow.CreateCell(2).SetCellValue("差值和");
newRow.CreateCell(3).SetCellValue("班级数");
newRow.CreateCell(4).SetCellValue("平均值");
DataView dvClass = dtTotal.DefaultView;
DataTable dataTableClasses = dvClass.ToTable(true, "teacher", "mobile");
for (var j = 0; j < dataTableClasses.Rows.Count; j++)
{
newRow = totalST.CreateRow(j + 1) as XSSFRow;
string teacher = dataTableClasses.Rows[j]["teacher"].ToString();
string mobile = dataTableClasses.Rows[j]["mobile"].ToString();
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue(teacher);
newRow.CreateCell(1).SetCellValue(mobile);
dvClass.RowFilter = "teacher='" + teacher + "' and mobile='" + mobile + "'";
DataTable newTable = dvClass.ToTable(false, "dvalue");
Single total = Single.Parse(newTable.Compute("sum(dvalue)", "").ToString());
int totalCount = newTable.Rows.Count;
newRow.CreateCell(2).SetCellValue(Math.Round(total, 2));
newRow.CreateCell(3).SetCellValue(totalCount);
newRow.CreateCell(4).SetCellValue(Math.Round(total / totalCount, 2));
}
}
public void TestMissingRAttribute()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFRow row = (XSSFRow)sheet.CreateRow(0);
XSSFCell a1 = (XSSFCell)row.CreateCell(0);
a1.SetCellValue("A1");
XSSFCell a2 = (XSSFCell)row.CreateCell(1);
a2.SetCellValue("B1");
XSSFCell a4 = (XSSFCell)row.CreateCell(4);
a4.SetCellValue("E1");
XSSFCell a6 = (XSSFCell)row.CreateCell(5);
a6.SetCellValue("F1");
assertCellsWithMissingR(row);
a2.GetCTCell().unsetR();
a6.GetCTCell().unsetR();
assertCellsWithMissingR(row);
wb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
assertCellsWithMissingR(row);
}
public static DataTable readExcel(string filePath, string sheetName, int headIndex = 0)
{
DataTable table = new DataTable();
try
{
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(sheetName);
XSSFRow headRow = (XSSFRow)sheet.GetRow(headIndex);
int columnCount = headRow.Cells.Count;
//获取标题名
for (int index = 0; index < headRow.Cells.Count; index++)
{
DataColumn dc = new DataColumn(headRow.GetCell(index).ToString());
table.Columns.Add(dc);
}
for (int i = sheet.FirstRowNum + 1 + headIndex; i <= sheet.LastRowNum; i++)
{
DataRow newRow = table.NewRow();
XSSFRow row = (XSSFRow)sheet.GetRow(i);
int isEmpty = 0;
if (row != null)
{
//复制整行数据
for (int j = 0; j < columnCount - 1; j++)
{
//if (row.GetCell(j).ToString()==string.Empty)
//{
// isEmpty++;
//}
newRow[j] = row.GetCell(j);
if (newRow[j].ToString() == string.Empty || newRow[j].ToString() == "NULL")
{
newRow[j] = null;
isEmpty++;
}
}
if (isEmpty == columnCount)
{
continue;
}
//添加到数据表中
table.Rows.Add(newRow);
}
}
}
}
catch (Exception exMsg)
{
throw (exMsg);
//throw (exMsg);
}
return(table);
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (XSSFCell xssfCell in row)
{
CT_Cell ctCell = xssfCell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
string formula1 = f.Value;
if (formula1.Length > 0)
{
string str = XSSFRowShifter.ShiftFormula(row, formula1, Shifter);
if (str != null)
{
f.Value = str;
}
}
if (f.isSetRef())
{
string formula2 = f.@ref;
string str = XSSFRowShifter.ShiftFormula(row, formula2, Shifter);
if (str != null)
{
f.@ref = str;
}
}
}
}
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null)
{
f.@ref = ShiftedRef;
}
}
}
}
}
//处理excel2007
private static DataTable ExcelToDataTableFirstRowAsHeader(XSSFSheet sheet, XSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
XSSFRow firstRow = sheet.GetRow(0) as XSSFRow;
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return(dt);
}
}
public static XSSFWorkbook BuildSwitchData <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
XSSFRow row_Title = (XSSFRow)sheet.CreateRow(0); //创建列头行
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
XSSFCell cell_Title = (XSSFCell)row_Title.CreateCell(ii); //创建单元格
//cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
//sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
for (int i = 0; i < list.Count; i++)
{
XSSFRow row_Content = (XSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
XSSFCell cell_Conent = (XSSFCell)row_Content.CreateCell(jj); //创建单元格
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
/// <summary>
/// Convert Excel sheets to DataTable list
/// </summary>
/// <param name="filename"></param>
/// <returns>list of datatable</returns>
public static List <DataTable> ExceltoDataTable(string filename)
{
XSSFWorkbook xssfwb;
List <DataTable> dts = new List <DataTable>();
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
for (int i = 0; i < xssfwb.NumberOfSheets; i++)
{
XSSFSheet sheet = (XSSFSheet)xssfwb.GetSheetAt(i);
DataTable dt = new DataTable();
int num = 0;
while (sheet.GetRow(num) != null)
{
if (dt.Columns.Count < sheet.GetRow(num).Cells.Count)
{
for (int j = 0; j < sheet.GetRow(num).Cells.Count; j++)
{
dt.Columns.Add("", typeof(string));
}
}
XSSFRow row = (XSSFRow)sheet.GetRow(num);
DataRow dr = dt.Rows.Add();
for (int k = 0; k < row.Cells.Count; k++)
{
XSSFCell cell = (XSSFCell)row.GetCell(k);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
dr[k] = cell.NumericCellValue;
break;
case CellType.String:
dr[k] = cell.StringCellValue;
break;
case CellType.Blank:
dr[k] = "";
break;
case CellType.Boolean:
dr[k] = cell.BooleanCellValue;
break;
}
}
}
num++;
}
dts.Add(dt);
}
return(dts);
}
private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter)
{
foreach (IRow r in sh)
{
XSSFRow row = (XSSFRow)r;
updateRowFormulas(row, Shifter);
}
}
public static void UpdateSheetFormulas(ISheet sh, FormulaShifter Shifter)
{
foreach (IRow r in sh)
{
XSSFRow row = (XSSFRow)r;
UpdateRowFormulas(row, Shifter);
}
}
/// <summary>
/// 生成对应的cs文件
/// </summary>
/// <param name="sheet"></param>
static void GenertorDao(XSSFSheet sheet)
{
CodeGenerator code = new CodeGenerator();
code.PrintLine("//***************************************************************");
code.PrintLine("//类名：", sheet.SheetName, "Ex");
code.PrintLine("//作者：", System.Environment.MachineName);
code.PrintLine("//日期：", DateTime.Now.ToString());
code.PrintLine("//作用：", sheet.SheetName, "的数据类");
code.PrintLine("//注意:", "不要在此类里面写代码!!!");
code.PrintLine("//***************************************************************");
code.PrintLine();
code.PrintLine("using System;");
code.PrintLine("using System.Collections.Generic;");
code.PrintLine();
code.PrintLine("public class ", sheet.SheetName, "{");
code.In();
#region 生成变量
XSSFRow typeRow = (XSSFRow)sheet.GetRow(0);
XSSFRow desRow = (XSSFRow)sheet.GetRow(1);
XSSFRow nameRow = (XSSFRow)sheet.GetRow(2);
for (int i = 0; i < typeRow.LastCellNum; i++)
{
string type = typeRow.GetCell(i).ToString();
string des = string.Empty;
if (desRow.GetCell(i) != null)
{
des = desRow.GetCell(i).ToString();
}
string name = nameRow.GetCell(i).ToString();
code.PrintLine("/// <summary>");
code.PrintLine("///" + des);
code.PrintLine("/// </summary>");
if (!type.Contains("List") && !type.Contains("Dictionary"))
{
code.PrintLine("public ", type, " ", name, ";");
}
else
{
code.PrintLine("public ", type, " ", name, "=new ", type, "();");
}
}
#endregion
code.Out();
code.PrintLine("}");
code.WriteFile(codePath + sheet.SheetName + ".cs");
Console.WriteLine(sheet.SheetName + ".cs 代码生成完成");
Log.Append(sheet.SheetName + ".cs 代码生成完成\n");
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
else
{
int prevNum = row.LastCellNum;
if (prevNum != -1)
{
_cellNum = (row as XSSFRow).GetCell(prevNum - 1, MissingCellPolicy.RETURN_NULL_AND_BLANK).ColumnIndex + 1;
}
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
private void CreateReportDataRow(XSSFRow row, DataRow x, int i)
{
row.CreateCell(00, CellType.NUMERIC).SetCellValue(i);
row.CreateCell(01, CellType.NUMERIC).SetCellValue(x.Field<int>("REGID"));
row.CreateCell(02, CellType.STRING).SetCellValue(x.Field<string>("CLINombre"));
row.CreateCell(03, CellType.STRING).SetCellValue(x.Field<string>("REGCaja"));
row.CreateCell(04, CellType.STRING).SetCellValue(x.Field<string>("REGNumero"));
row.CreateCell(05, CellType.NUMERIC).SetCellValue(x.Field<string>("REGCodigo"));
row.CreateCell(06, CellType.STRING).SetCellValue(x.Field<string>("RESDescripcion"));
row.CreateCell(07, CellType.NUMERIC).SetCellValue(Convert.ToDouble(x.Field<decimal>("REGValor")));
row.CreateCell(08, CellType.STRING).SetCellValue(x.Field<string>("REGObservacion"));
row.CreateCell(09, CellType.STRING).SetCellValue(!x.IsNull("REGFechaRecibido") ? x.Field<DateTime>("REGFechaRecibido").ToString("dd/MM/yyyy HH:mm.ss") : null);
row.CreateCell(10, CellType.STRING).SetCellValue(!x.IsNull("REGFechaEntrega") ? x.Field<DateTime>("REGFechaEntrega").ToString("dd/MM/yyyy HH:mm.ss") : null);
row.CreateCell(11, CellType.STRING).SetCellValue(x.Field<string>("ReclamanteTipoDocumento"));
row.CreateCell(12, CellType.STRING).SetCellValue(x.Field<string>("REGReclamanteNumeroDocumento"));
row.CreateCell(13, CellType.STRING).SetCellValue(x.Field<string>("REGReclamanteNombre"));
row.CreateCell(14, CellType.STRING).SetCellValue(x.Field<string>("PacienteTipoDocumento"));
row.CreateCell(15, CellType.STRING).SetCellValue(x.Field<string>("REGPacienteNumeroDocumento"));
row.CreateCell(16, CellType.STRING).SetCellValue(x.Field<string>("REGPacienteNombre"));
row.CreateCell(17, CellType.STRING).SetCellValue(x.Field<string>("UsuarioCreacion"));
row.CreateCell(18, CellType.STRING).SetCellValue(x.Field<DateTime>("REGFechaCreacion").ToString("dd/MM/yyyy HH:mm.ss"));
row.CreateCell(19, CellType.STRING).SetCellValue(x.Field<string>("REGUsuarioModificacion"));
row.CreateCell(20, CellType.STRING).SetCellValue(x.Field<DateTime>("REGFechaModificacion").ToString("dd/MM/yyyy HH:mm.ss"));
row.CreateCell(21, CellType.STRING).SetCellValue(!x.IsNull("RIMImagenCodigo") ? x.Field<Guid>("RIMImagenCodigo").ToString() : null);
row.CreateCell(22, CellType.STRING).SetCellValue(!x.IsNull("TIMCodigo") ? x.Field<string>("TIMCodigo") : null);
row.CreateCell(23, CellType.STRING).SetCellValue(!x.IsNull("TIMNombre") ? x.Field<string>("TIMNombre") : null);
row.CreateCell(24, CellType.STRING).SetCellValue(!x.IsNull("RIMImagenNombre") ? x.Field<string>("RIMImagenNombre") : null);
row.CreateCell(25, CellType.STRING).SetCellValue(!x.IsNull("RIMImagenTipo") ? x.Field<string>("RIMImagenTipo") : null);
row.CreateCell(26, CellType.NUMERIC).SetCellValue(!x.IsNull("RIMImagenPeso") ? x.Field<long>("RIMImagenPeso") : 0);
row.CreateCell(27, CellType.STRING).SetCellValue(!x.IsNull("ImagenUsuarioCreacion") ? x.Field<string>("ImagenUsuarioCreacion") : null);
row.CreateCell(28, CellType.STRING).SetCellValue(!x.IsNull("RIMFechaCreacion") ? x.Field<DateTime>("RIMFechaCreacion").ToString("dd/MM/yyyy HH:mm.ss") : null);
}
private void CreateReportResumeRow(XSSFRow row, DataRow x)
{
row.CreateCell(00, CellType.NUMERIC).SetCellValue(DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
row.CreateCell(01, CellType.NUMERIC).SetCellValue(x.Field<int>("Cajas"));
row.CreateCell(02, CellType.NUMERIC).SetCellValue(x.Field<int>("Registros"));
row.CreateCell(03, CellType.NUMERIC).SetCellValue(x.Field<int>("Imagenes"));
}
/**
* Shift a formula using the supplied FormulaShifter
*
* @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
* @param formula the formula to shift
* @param Shifter the FormulaShifter object that operates on the Parsed formula tokens
* @return the Shifted formula if the formula was Changed,
* <code>null</code> if the formula wasn't modified
*/
private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
String ShiftedFmla = null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
return ShiftedFmla;
}
private void assertCellsWithMissingR(XSSFRow row)
{
XSSFCell a1 = (XSSFCell)row.GetCell(0);
Assert.IsNotNull(a1);
XSSFCell a2 = (XSSFCell)row.GetCell(1);
Assert.IsNotNull(a2);
XSSFCell a5 = (XSSFCell)row.GetCell(4);
Assert.IsNotNull(a5);
XSSFCell a6 = (XSSFCell)row.GetCell(5);
Assert.IsNotNull(a6);
Assert.AreEqual(6, row.LastCellNum);
Assert.AreEqual(4, row.PhysicalNumberOfCells);
Assert.AreEqual(a1.StringCellValue, "A1");
Assert.AreEqual(a2.StringCellValue, "B1");
Assert.AreEqual(a5.StringCellValue, "E1");
Assert.AreEqual(a6.StringCellValue, "F1");
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
Assert.AreEqual(a1.GetReference(), "A1");
Assert.AreEqual(a2.GetReference(), "B1");
Assert.AreEqual(a5.GetReference(), "E1");
Assert.AreEqual(a6.GetReference(), "F1");
}
private void InitRows(CT_Worksheet worksheet)
{
_rows = new SortedDictionary<int, XSSFRow>();
tables = new Dictionary<String, XSSFTable>();
sharedFormulas = new Dictionary<int, CT_CellFormula>();
arrayFormulas = new List<CellRangeAddress>();
if (0 < worksheet.sheetData.SizeOfRowArray())
{
foreach (CT_Row row in worksheet.sheetData.row)
{
XSSFRow r = new XSSFRow(row, this);
if (!_rows.ContainsKey(r.RowNum))
_rows.Add(r.RowNum, r);
}
}
}
/**
* Shift a formula using the supplied FormulaShifter
*
* @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
* @param formula the formula to shift
* @param Shifter the FormulaShifter object that operates on the Parsed formula tokens
* @return the Shifted formula if the formula was Changed,
* <code>null</code> if the formula wasn't modified
*/
private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter)
{
ISheet sheet = row.Sheet;
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
try
{
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
String ShiftedFmla = null;
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
}
return ShiftedFmla;
}
catch (FormulaParseException fpe)
{
// Log, but don't change, rather than breaking
Console.WriteLine("Error shifting formula on row {0}, {1}", row.RowNum, fpe);
return formula;
}
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
if (f.t == ST_CellFormulaType.shared)
{
int si = (int)f.si;
CT_CellFormula sf = ((XSSFSheet)row.Sheet).GetSharedFormula(si);
sf.Value = (ShiftedFormula);
}
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null) f.@ref = ShiftedRef;
}
}
}
}
/**
* Create a new row within the sheet and return the high level representation
*
* @param rownum row number
* @return High level {@link XSSFRow} object representing a row in the sheet
* @see #RemoveRow(NPOI.SS.usermodel.Row)
*/
public virtual IRow CreateRow(int rownum)
{
CT_Row ctRow;
XSSFRow prev = _rows.ContainsKey(rownum) ? _rows[rownum] : null;
if (prev != null)
{
ctRow = prev.GetCTRow();
ctRow.Set(new CT_Row());
}
else
{
if (_rows.Count == 0 || rownum > GetLastKey(_rows.Keys))
{
// we can append the new row at the end
ctRow = worksheet.sheetData.AddNewRow();
}
else
{
// get number of rows where row index < rownum
// --> this tells us where our row should go
int idx = HeadMap(_rows, rownum).Count;
ctRow = worksheet.sheetData.InsertNewRow(idx);
}
}
XSSFRow r = new XSSFRow(ctRow, this);
r.RowNum = rownum;
_rows[rownum] = r;
return r;
}
private int WriteHidden(XSSFRow xRow, int rowIndex, bool hidden)
{
int level = xRow.GetCTRow().outlineLevel;
for (IEnumerator it = this.GetRowEnumerator(); it.MoveNext(); )
{
xRow = (XSSFRow)it.Current;
if (xRow.GetCTRow().outlineLevel >= level)
{
xRow.GetCTRow().hidden = (hidden);
rowIndex++;
}
}
return rowIndex;
}
