/**
* @return <c>null</c> if cell is1 missing, empty or blank
*/
private static String GetTargetFunctionName(NPOI.SS.UserModel.Row r)
{
if (r == null)
{
Console.Error.WriteLine("Warning - given null row, can't figure out function name");
return(null);
}
Cell cell = r.GetCell(SS.COLUMN_INDEX_FUNCTION_NAME);
if (cell == null)
{
Console.Error.WriteLine("Warning - NPOI.SS.UserModel.Row " + r.RowNum + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name");
return(null);
}
if (cell.CellType == NPOI.SS.UserModel.CellType.BLANK)
{
return(null);
}
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
return(cell.RichStringCellValue.String);
}
throw new AssertFailedException("Bad cell type for 'function name' column: ("
+ cell.CellType + ") row (" + (r.RowNum + 1) + ")");
}
private static Cell GetExpectedValueCell(NPOI.SS.UserModel.Row row, short columnIndex)
{
if (row == null)
{
return(null);
}
return(row.GetCell(columnIndex));
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Row row, int column)
{
NPOI.SS.UserModel.Cell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return(cell);
}
/// <summary>
/// Get a row from the spreadsheet, and Create it if it doesn't exist.
/// </summary>
/// <param name="rowCounter">The 0 based row number</param>
/// <param name="sheet">The sheet that the row is part of.</param>
/// <returns>The row indicated by the rowCounter</returns>
public static NPOI.SS.UserModel.Row GetRow(int rowCounter, HSSFSheet sheet)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return(row);
}
/// <summary>
/// 把DataTable 转为Excel 内容
/// </summary>
/// <param name="dt"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="book"></param>
/// <param name="sheetName"></param>
private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName)
{
//头部样式
CellStyle headstyle = book.CreateCellStyle();
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
Font headfont = book.CreateFont();
headfont.Boldweight = 700;
headstyle.SetFont(headfont);
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderTop = CellBorderType.THIN;
NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
header.Height = 20 * 20;
//表格内容样
CellStyle dataStyle = book.CreateCellStyle();
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.BorderTop = CellBorderType.THIN;
for (int i = 0; i < dt.Columns.Count; i++)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(i);
cell.CellStyle = headstyle;
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).CellStyle = dataStyle;
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}
/// <summary>
/// Creates a cell, gives it a value, and applies a style if provided
/// </summary>
/// <param name="row">the row to Create the cell in</param>
/// <param name="column">the column index to Create the cell in</param>
/// <param name="value">The value of the cell</param>
/// <param name="style">If the style is not null, then Set</param>
/// <returns>A new HSSFCell</returns>
public static NPOI.SS.UserModel.Cell CreateCell(NPOI.SS.UserModel.Row row, int column, String value, HSSFCellStyle style)
{
NPOI.SS.UserModel.Cell cell = GetCell(row, column);
cell.SetCellValue(new HSSFRichTextString(value));
if (style != null)
{
cell.CellStyle = (style);
}
return(cell);
}
public DataTable GetData(string filePath)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
using (NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0))
{
DataTable table = new DataTable();
NPOI.SS.UserModel.Row headerRow = sheet.GetRow(0); //第一行为标题行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
}
protected void CreateDataHeader(Sheet worksheet)
{
StyleManager.InitStaticStyle();
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 2));
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5));
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 6, 8));
int colindex = 0;
NPOI.SS.UserModel.Row header = worksheet.CreateRow(0);
StyleManager.SetCenterBoldStringCell(header.CreateCell(colindex++)).SetCellValue("Daily AR&AP Interface");
header.Height = 450;
NPOI.SS.UserModel.Row header1 = worksheet.CreateRow(1);
colindex = 0;
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Newegg.com.cn");
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("Posting Date: " + end);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue("MTD( " + begin + " - " + end + " )");
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header1.CreateCell(colindex++)).SetCellValue(string.Empty);
NPOI.SS.UserModel.Row header2 = worksheet.CreateRow(2);
colindex = 0;
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue(string.Empty);
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Legacy Data");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Retrieved from SAP Table");
StyleManager.SetCenterBoldStringCell(header2.CreateCell(colindex++)).SetCellValue("Balance");
NPOI.SS.UserModel.Row header3 = worksheet.CreateRow(3);
colindex = 0;
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("AcctType");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("DocumentType");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("CompanyCode");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(A)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(B)");
StyleManager.SetCenterBoldStringCell(header3.CreateCell(colindex++)).SetCellValue("(C=A-B)");
}
/**
* @param startRowIndex row index in the spReadsheet where the first function/operator is1 found
* @param testFocusFunctionName name of a single function/operator to test alone.
* Typically pass <c>null</c> to test all functions
*/
private void processFunctionGroup(int startRowIndex, String testFocusFunctionName)
{
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
int rowIndex = startRowIndex;
while (true)
{
NPOI.SS.UserModel.Row r = sheet.GetRow(rowIndex);
String targetFunctionName = GetTargetFunctionName(r);
if (targetFunctionName == null)
{
throw new AssertFailedException("Test spReadsheet cell empty on row ("
+ (rowIndex + 1) + "). Expected function name or '"
+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
}
if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL))
{
// found end of functions list
break;
}
if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.InvariantCultureIgnoreCase))
{
// expected results are on the row below
NPOI.SS.UserModel.Row expectedValuesRow = sheet.GetRow(rowIndex + 1);
if (expectedValuesRow == null)
{
int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row
throw new AssertFailedException("Missing expected values row for function '"
+ targetFunctionName + " (row " + missingRowNum + ")");
}
switch (ProcessFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow))
{
case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
case Result.NO_EVALUATIONS_FOUND: // do nothing
break;
default:
throw new Exception("unexpected result");
}
}
rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION;
}
}
protected void CreateDataCells(Sheet worksheet, List <SAPInterfaceExchangeInfo> dataList)
{
ushort currentRowIndex = 4;
ushort currentColIndex = 0;
dataList.ForEach(delegate(SAPInterfaceExchangeInfo item)
{
NPOI.SS.UserModel.Row data = worksheet.CreateRow(currentRowIndex++);
currentColIndex = 0;
StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.AcctTypeDisplay);
StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.DocumentType);
StyleManager.SetCenterBoldStringCell(data.CreateCell(currentColIndex++)).SetCellValue(item.CompanyCode);
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.Legacy_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.SAP_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.DateBalance));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDLegacy_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDSAP_GLAmount));
StyleManager.SetRightDataCell(data.CreateCell(currentColIndex++)).SetCellValue(Convert.ToDouble(item.MTDData.MTDBalance));
});
}
/// <summary>
/// Gets the cell to manage it.
/// </summary>
/// <param name="rowPos">The row pos.</param>
/// <param name="columnPos">The column pos.</param>
/// <returns>Cell of the spreadsheet</returns>
public NPOI.SS.UserModel.Cell GetCell(int rowPos, int columnPos)
{
NPOI.SS.UserModel.Row row = _sheet.GetRow(rowPos);
if (row == null)
{
AddRow(rowPos);
row = _sheet.GetRow(rowPos);
}
//Creating the cell
NPOI.SS.UserModel.Cell cell = row.GetCell(columnPos);
if (cell == null)
{
row.CreateCell(columnPos);
cell = row.GetCell(columnPos);
}
return(cell);
}
/**
*
* @return a constant from the local Result class denoting whether there were any evaluation
* cases, and whether they all succeeded.
*/
private int ProcessFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName,
NPOI.SS.UserModel.Row formulasRow, NPOI.SS.UserModel.Row expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
int endcolnum = formulasRow.LastCellNum;
//evaluator.SetCurrentRow(formulasRow);
// iterate across the row for all the evaluation cases
for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
Cell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
NPOI.SS.UserModel.CellValue actualValue = evaluator.Evaluate(c);
Cell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
try
{
ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
expectedValueCell, actualValue);
_evaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (AssertFailedException)
{
_evaluationFailureCount++;
//printShortStackTrace(System.err, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return(result);
}
public void TestCountifFromSpreadsheet()
{
String FILE_NAME = "countifExamples.xls";
int START_ROW_IX = 1;
int COL_IX_ACTUAL = 2;
int COL_IX_EXPECTED = 3;
int failureCount = 0;
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(FILE_NAME);
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
int maxRow = sheet.LastRowNum;
for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(rowIx);
if (row == null)
{
continue;
}
Cell cell = row.GetCell(COL_IX_ACTUAL);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
double actualValue = cv.NumberValue;
double expectedValue = row.GetCell(COL_IX_EXPECTED).NumericCellValue;
if (actualValue != expectedValue)
{
Console.Error.WriteLine("Problem with Test case on row " + (rowIx + 1) + " "
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
failureCount++;
}
}
if (failureCount > 0)
{
throw new AssertFailedException(failureCount + " countif evaluations failed. See stderr for more details");
}
}
protected void ExportResult(IList <Bill> billList)
{
HSSFWorkbook excel = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = excel.CreateSheet("BILL");
NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("BILL NO");
row.CreateCell(1).SetCellValue("ASN");
row.CreateCell(2).SetCellValue("零件");
row.CreateCell(3).SetCellValue("单价");
row.CreateCell(4).SetCellValue("开票数");
row.CreateCell(5).SetCellValue("金额");
int rowNum = 1;
foreach (Bill bill in billList)
{
foreach (BillDetail bd in bill.BillDetails)
{
NPOI.SS.UserModel.Row _row = sheet.CreateRow(rowNum);
_row.CreateCell(0).SetCellValue(bd.Bill.BillNo);
_row.CreateCell(1).SetCellValue(bd.IpNo);
_row.CreateCell(2).SetCellValue(bd.ActingBill.Item.Code);
_row.CreateCell(3).SetCellValue((double)bd.UnitPrice);
_row.CreateCell(4).SetCellValue((double)bd.BilledQty);
_row.CreateCell(5).SetCellValue((double)bd.Amount);
rowNum++;
}
}
MemoryStream ms = new MemoryStream();
excel.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=BillResult.xls"));
Response.BinaryWrite(ms.ToArray());
excel = null;
ms.Close();
ms.Dispose();
}
public override void OutPut(DataTable dt)
{
string fileExt = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileOutPut = base.m_OutputFilePath.Insert(m_OutputFilePath.LastIndexOf("."), fileExt);
string saveFileName = fileOutPut;
//SaveFileDialog saveDialog = new SaveFileDialog();
//saveDialog.DefaultExt = "xls";
//saveDialog.Filter = "Excel文件|*.xls";
//saveDialog.FileName = fileName;
//saveDialog.ShowDialog();
//saveFileName = saveDialog.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
Sheet sheet = workbook.CreateSheet("Sheet1");
NPOI.SS.UserModel.Row dataTableName = sheet.CreateRow(0);
NPOI.SS.UserModel.Cell cellTableName = dataTableName.CreateCell(0);
cellTableName.SetCellValue(dt.TableName);
//sheet.SetActiveCellRange(0, 2, 0, dt.Columns.Count);
int rangeID = sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, dt.Columns.Count));
NPOI.SS.UserModel.Row dataRowColumnName = sheet.CreateRow(3);
for (int j = 0; j < dt.Columns.Count; j++)
{
NPOI.SS.UserModel.Cell cellName = dataRowColumnName.CreateCell(j);
cellName.SetCellValue(dt.Columns[j].ColumnName);
NPOI.SS.UserModel.Name sheetName = workbook.CreateName();
sheetName.NameName = dt.Columns[j].ColumnName;
sheetName.SheetIndex = 0;
}
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
for (int i = 0; i < rowCount; i++)
{
NPOI.SS.UserModel.Row dataRow = sheet.CreateRow(4 + i);
for (int j = 0; j < colCount; j++)
{
NPOI.SS.UserModel.Cell cell = dataRow.CreateCell(j);
if (dt.Rows[i][j].GetType().Equals(typeof(DateTime)))
{
cell.SetCellValue(((DateTime)(dt.Rows[i][j])).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(dt.Rows[i][j].ToString()); //项目序号
}
}
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
if (MessageBox.Show("Excel导出成功：" + fileOutPut + "\r\n是否要打开？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(fileOutPut);
}
}
/// <summary>
/// Adds the row into sheet.
/// </summary>
/// <param name="pos">The row position.</param>
public void AddRow(int pos)
{
//Creating row to insert data entries
NPOI.SS.UserModel.Row row = _sheet.CreateRow(pos);
}
/// <summary>
/// Create a cell, and give it a value.
/// </summary>
/// <param name="row">the row to Create the cell in</param>
/// <param name="column">the column index to Create the cell in</param>
/// <param name="value">The value of the cell</param>
/// <returns>A new HSSFCell.</returns>
public static NPOI.SS.UserModel.Cell CreateCell(NPOI.SS.UserModel.Row row, int column, String value)
{
return(CreateCell(row, column, value, null));
}
/// <summary>
/// 根据Excel获取集合信息
/// </summary>
/// <param name="fileName">Excel存储路径</param>
/// <returns></returns>
public List <IMovieShowList.MovieShow> GetList4Excel(string fileName)
{
List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>();
HSSFWorkbook wk = null;
using (FileStream fs = System.IO.File.Open(fileName, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
wk = new HSSFWorkbook(fs);
fs.Close();
}
HSSFSheet sheet = (HSSFSheet)wk.GetSheetAt(0);
if (sheet != null)
{
int rowIndex = 0;
NPOI.SS.UserModel.Row row = null;
while ((row = sheet.GetRow(rowIndex)) != null)
{
try
{
Cell c1 = row.GetCell(0);
string room = c1.RichStringCellValue.String;
Cell c2 = row.GetCell(1);
string tim = c2.RichStringCellValue.String;
Cell c3 = row.GetCell(2);
string name = c3.RichStringCellValue.String;
if (string.IsNullOrWhiteSpace(room) || string.IsNullOrWhiteSpace(tim) || string.IsNullOrWhiteSpace(name))
{
rowIndex++;
continue;
}
string time = ParseBeginTime(tim);
list.Add(new IMovieShowList.MovieShow()
{
Room = room,
BeginTime = time,
Name = name
});
rowIndex++;
}
catch (Exception ex)
{
isOk = false;
Msg = "xcel文件中的时间有错误,在" + (rowIndex + 1) + "行" + Environment.NewLine + ex.Message;
break;
}
}
sheet.Dispose();
}
return(list);
}
public static void ExportToExcelExtended(DataTable table, string Name)
{
//ExcelHelper excel = new ExcelHelper();
//excel.CreateFile(Name);
//excel.CreateSheet(Name);
InitializeWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("new sheet");
//ExcelDocument document = new ExcelDocument();
//document.UserName = "******";
//document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
//document.ColumnWidth(0, 120);
//document.ColumnWidth(1, 80);
//document[0, 0].Value = Name;
//document[0, 0].Font = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold);
//document[0, 0].ForeColor = ExcelColor.DarkRed;
//document[0, 0].Alignment = Alignment.Centered;
//document[0, 0].BackColor = ExcelColor.Silver;
int i = 1; //стрічка
int j = 1; //колонка
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(j);
cell.SetCellValue(column.ColumnName);
j++;
}
j = 1;
foreach (DataRow r in table.Rows)
{
j = 1;
//NPOI.SS.UserModel.Row row = excel.CreateRow(i);
NPOI.SS.UserModel.Row row = sheet.CreateRow(i);
foreach (DataColumn column in table.Columns)
{
NPOI.SS.UserModel.Cell cell = row.CreateCell(j);
Type dataType = column.DataType;
switch (dataType.Name)
{
case "String":
{
cell.SetCellValue(r.ItemArray[j - 1].ToString());
break;
}
case "DateTime":
{
DateTime date = (Convert.ToDateTime(r.ItemArray[j - 1]));
cell.SetCellValue(date);
CellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyyy-MM-dd h:mm:ss");
cell.CellStyle = cellStyle;
break;
}
case "Decimal":
{
if (r.ItemArray[j - 1] != null)
{
if (r.ItemArray[j - 1] != null)
{
if (r.ItemArray[j - 1].ToString().Length > 0)
{
cell.SetCellValue(Convert.ToDouble(r.ItemArray[j - 1]));
}
}
}
break;
}
default:
{
cell.SetCellValue(r.ItemArray[j - 1].ToString());
break;
}
}
//excel.SetCellValue( r.ItemArray[j]
j++;
}
//foreach (object item in r.ItemArray)
//{
// //document.Cell(i,j).Format
// excel.CreateCell(j);
// excel.SetCellValue( item == null ? "" : (item);
// j++;
//}
i++;
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.FileName = Name;
saveFileDialog1.DefaultExt = "xls";
saveFileDialog1.ShowDialog();
//FileStream stream = new FileStream(saveFileDialog1.FileName, FileMode.Create);
WriteToFile(saveFileDialog1.FileName);
//System.Diagnostics.Process.Start(saveFileDialog1.FileName);
}
/// <summary>
/// 导出DataTable到Excel文件
/// </summary>
/// <param name="filename">文件名称</param>
/// <param name="dtTable">数据DataTable</param>
/// <returns>文件保存路径</returns>
public string ExportFile(string filename, List <dynamic> data, string webPath = "")
{
NPOI.SS.UserModel.Row rowItem = null;
int rowCnt = 0;
foreach (dynamic dr in data)
{
if (rowCnt == 0)
{
//创建表头
rowItem = _sheet.CreateRow(rowCnt);
int i = 0;
foreach (KeyValuePair <string, object> dynamicItem in dr)
{
if (_colName.ContainsKey(dynamicItem.Key))
{
rowItem.CreateCell(i).SetCellValue(_colName[dynamicItem.Key]);
}
else
{
rowItem.CreateCell(i).SetCellValue(dynamicItem.Key);
}
i++;
}
rowCnt = rowCnt + 1;
}
rowItem = _sheet.CreateRow(rowCnt);
int k = 0;
foreach (KeyValuePair <string, object> dynamicItem in dr)
{
string sItemVal = "";
if (dynamicItem.Value != null)
{
sItemVal = dynamicItem.Value.ToString().Trim();
}
rowItem.CreateCell(k).SetCellValue(sItemVal);
k++;
}
rowCnt = rowCnt + 1;
}
//保存文件
if (webPath == "")
{
webPath = System.Web.HttpContext.Current.Server.MapPath("/");
}
//文件名
string strFileName = string.Format("{0}_{1}_{2}.xls", filename.ToString(),
DateTime.Now.ToString("yyyyMMddHHmm"), CommonLib.Helper.GetRandomNum());
//文件路径
string strFilePath = "\\ExportFile\\" + _className + "\\" + DateTime.Now.ToString("yyyyMM") + "\\";
if (!Directory.Exists(webPath + strFilePath))
{
Directory.CreateDirectory(webPath + strFilePath);
}
var fileInfo = new FileStream(webPath + strFilePath + strFileName, FileMode.Create);
Workbook.Write(fileInfo);
fileInfo.Close();
return(strFilePath + strFileName);
}
