public static CSException <List <TH_HAZALOCA> > BuildListByNPOISheet(NPOI.HSSF.UserModel.HSSFSheet HSSFSheet)
{
try
{
var db = new SAFEDB();
var rowcount = HSSFSheet.LastRowNum + 1;
var hazaList = new List <TH_HAZALOCA>();
for (var i = 0; i < rowcount; i++)
{
var row = HSSFSheet.GetRow(i);
var deptCode = row.Cells[0].StringCellValue;
var locaName = row.Cells[1].StringCellValue;
var newLoca = new TH_HAZALOCA(locaName, deptCode);
var allowed = newLoca.IsAllowed();
if (!allowed.Flag)
{
db.TH_HAZALOCA.Add(newLoca);
db.SaveChanges();
}
else
{
throw new Exception(allowed.Msg);
}
}
return(new CSException <List <TH_HAZALOCA> >(true, rowcount.ToString(), hazaList));
}
catch (Exception ex)
{
return(new CSException <List <TH_HAZALOCA> >(ex.Message, new List <TH_HAZALOCA>()));
}
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
private void SaveOrInsertSheet_Source(HSSFSheet u_sheet)
{
Database.MSSQL DB = new Database.MSSQL("Web");
List<SqlParameter> PMS = new List<SqlParameter>();
//因為要讀取的資料列不包含標頭，所以i從u_sheet.FirstRowNum + 1開始讀
/*一列一列地讀取資料*/
int Company_check = 0;
for (int i = u_sheet.FirstRowNum + 1; i <= u_sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)u_sheet.GetRow(i);//取得目前的資料列 2015/03/18[新增資料區段]
for (int j = 0; j < Stock_Num.Rows.Count; j++)
{
if (Equals(Stock_Num.Rows[j][0], row.GetCell(0).ToString()))
{
Company_check = 1;
}
}
if (Company_check == 1)
{
PMS = new List<SqlParameter>();
PMS.Add(new SqlParameter("@證券代號", row.GetCell(0).ToString()));
PMS.Add(new SqlParameter("@成交股數", Convert.ToInt32(row.GetCell(2).ToString())));
PMS.Add(new SqlParameter("@成交筆數", Convert.ToInt32(row.GetCell(3).ToString())));
PMS.Add(new SqlParameter("@成交金額", Convert.ToSingle(row.GetCell(4).ToString())));
PMS.Add(new SqlParameter("@開盤價", Convert.ToSingle(row.GetCell(5).ToString())));
PMS.Add(new SqlParameter("@最高價", Convert.ToSingle(row.GetCell(6).ToString())));
PMS.Add(new SqlParameter("@最低價", Convert.ToSingle(row.GetCell(7).ToString())));
PMS.Add(new SqlParameter("@收盤價", Convert.ToSingle(row.GetCell(8).ToString())));
PMS.Add(new SqlParameter("@最後揭示買價", Convert.ToSingle(row.GetCell(11).ToString())));
PMS.Add(new SqlParameter("@最後揭示買量", Convert.ToInt32(row.GetCell(12).ToString())));
PMS.Add(new SqlParameter("@最後揭示賣價", Convert.ToSingle(row.GetCell(13).ToString())));
PMS.Add(new SqlParameter("@最後揭示賣量", Convert.ToInt32(row.GetCell(14).ToString())));
PMS.Add(new SqlParameter("@資料日期", Convert.ToDateTime(DataTime_Source_txt.Text)));
PMS.Add(new SqlParameter("@下載日期", dt));
DB.ExecutionStoredProcedure("[Source_Insert]", PMS.ToArray());
}
Company_check = 0;
}
}
private void SaveOrInsertSheet(HSSFSheet u_sheet)
{
Database.MSSQL DB = new Database.MSSQL("Web");
List<SqlParameter> PMS = new List<SqlParameter>();
//因為要讀取的資料列不包含標頭，所以i從u_sheet.FirstRowNum + 1開始讀
/*一列一列地讀取資料*/
int Company_check = 0;
for (int i = u_sheet.FirstRowNum + 1; i <= u_sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)u_sheet.GetRow(i);//取得目前的資料列 2015/03/18[新增資料區段]
for (int j = 0; j < Stock_Num.Rows.Count; j++ )
{
if (Equals(Stock_Num.Rows[j][0], row.GetCell(0).ToString()))
{
Company_check = 1;
}
}
if (Company_check == 0)
{
PMS = new List<SqlParameter>();
PMS.Add(new SqlParameter("@C_ID", row.GetCell(0).ToString()));
PMS.Add(new SqlParameter("@C_Name", row.GetCell(1).ToString()));
DB.ExecutionStoredProcedure("[GetStockNum_Insert]", PMS.ToArray());
}
PMS = new List<SqlParameter>();
PMS.Add(new SqlParameter("@證券代號", row.GetCell(0).ToString()));
PMS.Add(new SqlParameter("@本益比", Convert.ToSingle(row.GetCell(2).ToString())));
PMS.Add(new SqlParameter("@殖利率", Convert.ToSingle(row.GetCell(3).ToString())));
PMS.Add(new SqlParameter("@股價淨值比", Convert.ToSingle(row.GetCell(4).ToString())));
PMS.Add(new SqlParameter("@資料日期", Convert.ToDateTime(DataTime_PYS_txt.Text)));
PMS.Add(new SqlParameter("@下載日期", dt));
DB.ExecutionStoredProcedure("[PYS_Insert]", PMS.ToArray());
Company_check = 0;
}
}
public static void populateStrategyDetailData(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<ArchitecturalStrategy> data)
{
#region workbookStyles
//add Styles to workbook
HSSFCellStyle styleMiddle = wb.CreateCellStyle();
styleMiddle.Alignment = CellHorizontalAlignment.CENTER;
HSSFCellStyle styleLeftWrap = wb.CreateCellStyle();
styleLeftWrap.Alignment = CellHorizontalAlignment.LEFT;
styleMiddle.VerticalAlignment = CellVerticalAlignment.CENTER;
styleLeftWrap.WrapText = true; //wrap the text in the cell
//----------------------------------------------------------
//font style1: italic, blue color, fontsize=20
HSSFFont font1 = wb.CreateFont();
font1.Color = HSSFColor.BLUE.index;
font1.IsItalic = true;
font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font1.Underline = (byte)HSSFBorderFormatting.BORDER_THIN;
// font1.Underline = (byte)FontUnderlineType.DOUBLE;
// font1.FontHeightInPoints = 20;
//bind font with styleItalicBold
HSSFCellStyle italicBold = wb.CreateCellStyle();
italicBold.SetFont(font1);
//----------------------------------------------------------
//bind font with styleItalicBold
HSSFCellStyle underline = wb.CreateCellStyle();
underline.BorderBottom = CellBorderType.THIN;
underline.BottomBorderColor = HSSFColor.BLUE_GREY.index;
HSSFCellStyle topline = wb.CreateCellStyle();
topline.BorderTop = CellBorderType.THIN;
topline.TopBorderColor = HSSFColor.BLUE_GREY.index;
#endregion
//set headerRow and 1st column
const int maxRows = 65536; //npoi uses excel 2003
int hRowNum = 4; //row starts at 0.
int startCol = 0;
int errorRow = 2; //note errors
int errorCol = 6; //note errors
HSSFRow headerRow = sheet.GetRow(hRowNum);
int colIndex = startCol;
#region Headers
//date
sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);
//Title
sheet.GetRow(1).GetCell(1).SetCellValue(data.FirstOrDefault().Project.Name);
// handling headers.
headerRow.GetCell(colIndex).SetCellValue("Strategy");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Name");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Description");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Scenarios Affected");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Current Response");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Expected Response");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Current Utility");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Expected Utility");
colIndex++;
#endregion //headers
#region populateData
// foreach (DataColumn column in propertyInfos)
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = hRowNum + 1;
HSSFRow dataRow = sheet.CreateRow(rowIndex);
Boolean newStrategyRow = true;
var i = 0; //index for loops
try
{
foreach (var item in data)
{
dataRow = sheet.CreateRow(rowIndex);
if (rowIndex < maxRows - 1)
{
//write each field
newStrategyRow = true;
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue(item.ID);
dataRow.GetCell(colIndex).CellStyle = topline;
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Name.ToString());
dataRow.GetCell(colIndex).CellStyle = topline;
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Description.ToString());
dataRow.GetCell(colIndex).CellStyle = topline;
colIndex++;
var subcol = colIndex;
foreach (var s in item.ExpectedUtilities)
{
dataRow = sheet.CreateRow(rowIndex);
colIndex = subcol;
var currentUtil = s.Scenario.Utilities.Where(y => y.QualityAttributeResponseTypeID == "C").FirstOrDefault();
//ID of scenario affected
dataRow.CreateCell(colIndex).SetCellValue(s.Scenario.ID);
colIndex++;
//current description
dataRow.CreateCell(colIndex).SetCellValue(currentUtil.Description.ToString());
dataRow.GetCell(colIndex).CellStyle = styleLeftWrap;
colIndex++;
//expected description
dataRow.CreateCell(colIndex).SetCellValue(s.ExpectedUtilityDescription.ToString());
colIndex++;
//current utility
dataRow.CreateCell(colIndex).SetCellValue(currentUtil.Utility1.Value);
colIndex++;
//expected utility
dataRow.CreateCell(colIndex).SetCellValue(s.ExpectedUtility1.Value);
#region Add formats to top of a strategy
if (newStrategyRow) //set formats for new strat row
{ i = subcol;
while (i <= colIndex)
{
dataRow.GetCell(i).CellStyle = topline;
i++;
}
}
#endregion
rowIndex++;
newStrategyRow = false;
if (rowIndex >= maxRows - 1)
{throw new InvalidDataException();}
}//end expected utilities
#region Add formats to bottom of strategy
dataRow = sheet.CreateRow(rowIndex);
i = startCol;
while (i <= colIndex)//add formats to subsection w/utility info
{
dataRow.CreateCell(i).CellStyle = underline;
i++;
}
#endregion
//end of strategy
rowIndex++;
}//end if check max rows
#endregion
#region errors
else
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue("Dataset exceeds maximum number of rows");
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Data Exceeds max records");
}
}//end data loop
}// end try
catch
{
colIndex = startCol;
if (sheet.GetRow(errorRow).LastCellNum >= errorCol) //error cell exists
{
sheet.GetRow(errorRow).GetCell(errorCol).SetCellValue("Error Occured");
}
else
{//create cell for error message
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Error Occured");
}
}
#endregion
}
/// <summary>
/// 生成子表数据Excel
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
private int CreateSonTable(HSSFSheet sheet, int rowIndex)
{
var hamwitemArry = _hawbDataSource.HAWBItems;
if (hamwitemArry == null || hamwitemArry.Count == 0) return 0;
var currentRow = rowIndex;
var lastIndexRow = 0;
var sourcerow = sheet.GetRow(rowIndex);
foreach (var hamwitem in hamwitemArry)
{
if (currentRow != rowIndex)
{
MyInsertRow(sheet, currentRow, 1, sourcerow);
}
var row = sheet.GetRow(currentRow);
row.Height = 840;
if (!string.IsNullOrEmpty(hamwitem.Name) && !string.IsNullOrEmpty(hamwitem.Remark))
{
row.GetCell(3).SetCellValue(hamwitem.Name.ToUpper() + hamwitem.Remark.ToUpper());
}
else
{
row.GetCell(3).SetCellValue(hamwitem.Name + hamwitem.Remark);
}
row.GetCell(5).SetCellValue(hamwitem.Piece);
row.GetCell(6).SetCellValue(Convert.ToString(hamwitem.UnitAmount));
row.GetCell(7).SetCellValue(Convert.ToString(hamwitem.TotalAmount));
lastIndexRow = row.RowNum;
currentRow += 1;
}
var tempRow = sheet.GetRow(rowIndex);
tempRow.GetCell(8).SetCellValue(Convert.ToString(_hawbDataSource.TotalWeight));
var totalValue = Convert.ToString(hamwitemArry.Sum(p => p.TotalAmount));
tempRow.GetCell(9).SetCellValue(totalValue);
if (hamwitemArry.Count < 6)
{
var count = 6 - hamwitemArry.Count;
for (var i = 0; i < count; i++)
{
MyInsertRow(sheet, currentRow, 1, sourcerow);
var row = sheet.GetRow(currentRow);
lastIndexRow = row.RowNum;
currentRow += 1;
}
}
sheet.AddMergedRegion(new Region(rowIndex, 8, lastIndexRow, 8));
sheet.AddMergedRegion(new Region(rowIndex, 9, lastIndexRow, 9));
//var deleteRow = sheet.GetRow(rowIndex);
//DeleteRow(sheet, deleteRow);
//lastIndexRow=lastIndexRow - 1;
return lastIndexRow;
}
private void FillSheetWithData(HSSFWorkbook workbook, HSSFSheet sheet, Items data)
{
//fill date of generating
SetValue(sheet, _settings.CreateDate, DateTime.Today);
//fill order dates and delivery dates for each order
FillOrdersDates(sheet, data.Orders);
//fill data cells
for (var i = 0; i < data.Rows.Count; i++)
{
var dataRow = data.Rows[i];
var row = CopyRow(workbook, sheet, _settings.FirstRow, _settings.FirstRow + i + 1);
//fill data from data contract first
SetRowStringValue(row, _settings.Values.Code, dataRow.Code);
SetRowStringValue(row, _settings.Values.Name, dataRow.Name);
SetRowNumericValue(row, _settings.Values.KgPerUnit, dataRow.KgPerUnit);
SetRowNumericValue(row, _settings.Values.Usage2010, dataRow.Usages.ContainsKey("2010") ? dataRow.Usages["2010"] : 0);
SetRowNumericValue(row, _settings.Values.Usage2011, dataRow.Usages.ContainsKey("2011") ? dataRow.Usages["2011"] : 0);
SetRowNumericValue(row, _settings.Values.OnStock, dataRow.OnStock);
for (var j = 0; j < _settings.Values.Ordered.Count; j++)
{
if (j == _settings.Orders.Count) throw new IndexOutOfRangeException("Orders count is bigger then template can process");
if (j >= data.Orders.Count)
{
SetRowNumericValue(row, _settings.Values.Ordered[j], 0);
continue;
}
var order = dataRow.Ordered.SingleOrDefault(o => o.Id == data.Orders[j].Id);
SetRowNumericValue(row, _settings.Values.Ordered[j], order != null ? order.Value : 0);
}
//fill formulas to other specific fields
foreach (var formula in _settings.Formulas)
SetRowFormula(row, formula.Cell, string.Format(formula.Value, _settings.FirstRow + i + 2));
}
//set total order sum at the end of the document
SetRowFormula((HSSFRow)sheet.GetRow(_settings.FirstRow + data.Rows.Count + 3),
_settings.OrderedSum.Cell,
string.Format(_settings.OrderedSum.Value, _settings.FirstRow + 2, _settings.FirstRow + data.Rows.Count + 1));
}
private static HSSFCell GetCell(HSSFSheet sheet, string cellCoordinates)
{
if (!cellCoordinates.Contains(",")) throw new FormatException(string.Format("Invalid cell coordinates: {0}!", cellCoordinates));
var coordinates = cellCoordinates.Split(',');
return (HSSFCell)((HSSFRow)sheet.GetRow(Convert.ToInt32(coordinates[0]))).GetCell(Convert.ToInt32(coordinates[1]));
}
private static string MakeFileExcelSample(CMixExcel mixExcel, ref int sheetsNumber)
{
string result = string.Empty;
Stream _stream = null;
string fileNameTail = mixExcel.PathFile.Substring(mixExcel.PathFile.LastIndexOf('.'));
string samplefileName = mixExcel.PathFile.Substring(0, mixExcel.PathFile.LastIndexOf('.'));
string pathsamplefileName = String.Format("{0}_Sample{1}", samplefileName, fileNameTail);
FileStream fstream = File.Open(pathsamplefileName, FileMode.OpenOrCreate);
fstream.Close();
File.Copy(mixExcel.PathFile, pathsamplefileName, true);
CMixExcel mixExcelSample = new CMixExcel(pathsamplefileName);
if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage)
{
#region Excel 2007++
ExcelPackage excelSamplePackage = (ExcelPackage)mixExcelSample.ExcelMixCore;
ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore;
ExcelWorksheets workSheetsSample = excelSamplePackage.Workbook.Worksheets;
ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets;
sheetsNumber = workSheetsSample.Count;
for (int i = 1; i <= workSheetsSample.Count; i++)
{
var worksheetSample = workSheetsSample[i];
var worksheet = workSheets[i];
System.Xml.XmlNodeList test = worksheetSample.WorksheetXml.GetElementsByTagName("dimension");
if (test != null && test.Count > 0)
{
System.Xml.XmlNode value = test[0];
//<dimension ref="A1:J17" />
string outerXml = value.OuterXml.Substring("<dimension ref=\"".Length);
string rangesize = outerXml.Substring(0, outerXml.IndexOf('"'));
if (rangesize != "A1")
{
string[] arrayRange = rangesize.Split(':');
int indextemp = 0; int temp;
string MinRow = string.Empty, MaxRow = string.Empty;
int MinColumn = 0, MaxColumn = 0;
string range1 = arrayRange[0];
while (!int.TryParse(range1[indextemp].ToString(), out temp) && indextemp < range1.Length)
{
MinColumn += (System.Text.Encoding.ASCII.GetBytes(range1[indextemp].ToString())[0] - 64);
indextemp++;
}
MinRow = range1.Substring(indextemp);
indextemp = 0;
string range2 = arrayRange[1];
while (!int.TryParse(range2[indextemp].ToString(), out temp) && indextemp < range2.Length)
{
MaxColumn += (System.Text.Encoding.ASCII.GetBytes(range2[indextemp].ToString())[0] - 64);
indextemp++;
}
MaxRow = range2.Substring(indextemp);
if (!string.IsNullOrEmpty(MinRow) && MinColumn > 0 && !string.IsNullOrEmpty(MaxRow) && MaxColumn > 0)
{
int intMinRow = int.Parse(MinRow);
int intMaxRow = int.Parse(MaxRow);
for (int r = intMinRow; r <= intMaxRow; r++)
{
for (int c = MinColumn; c <= MaxColumn + 1; c++)
{
if (c > MinColumn)
{
worksheetSample.Cells[r, c].Value = worksheet.Cells[r, c - 1].Value;
}
else if (r == intMinRow)
{
worksheetSample.Cells[r, c].Value = "Heading";
}
else if (r == intMinRow + 1)
{
worksheetSample.Cells[r, c].Value = "Schema";
}
else
{
worksheetSample.Cells[r, c].Value = string.Empty;
}
}
}
}
}//(rangesize != "A1")
}
}
excelSamplePackage.Save();
_stream = excelSamplePackage.Stream;
#endregion
}
else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook)
{
#region Excel 2003--
HSSFWorkbook hssWorkbookSample = (HSSFWorkbook)mixExcelSample.ExcelMixCore;
HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore;
sheetsNumber = hssWorkbookSample.NumberOfSheets;
for (int i = 0; i < hssWorkbookSample.NumberOfSheets; i++)
{
NPOI.HSSF.UserModel.HSSFSheet excelSheetSample = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbookSample.GetSheetAt(i);
NPOI.HSSF.UserModel.HSSFSheet excelSheet = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbook.GetSheetAt(i);
NPOI.HSSF.Record.DimensionsRecord sheetDementionSample = excelSheetSample.Sheet.Dimensions;
NPOI.HSSF.Record.DimensionsRecord sheetDemention = excelSheet.Sheet.Dimensions;
for (int r = sheetDementionSample.FirstRow; r <= sheetDementionSample.LastRow; r++)
{
IRow rowSample = excelSheetSample.GetRow(r);
if (rowSample != null)
{
excelSheetSample.RemoveRow(rowSample);
}
}
for (int r = sheetDemention.FirstRow; r <= sheetDemention.LastRow; r++)
{
int maxCol = sheetDementionSample.LastCol;
int minCol = sheetDementionSample.FirstCol;
for (int c = minCol; c <= maxCol + 1; c++)
{
try
{
IRow rowSample = excelSheetSample.GetRow(r);
IRow row = excelSheet.GetRow(r);
if (row != null)
{
ICell excelSheetGetRowGetCellSample = rowSample.GetCell(c);
if (excelSheetGetRowGetCellSample == null)
{
excelSheetGetRowGetCellSample = rowSample.CreateCell(c);
}
if (c > sheetDementionSample.FirstCol)
{
ICell excelSheetGetRowGetCell = row.GetCell(c - 1);
if (excelSheetGetRowGetCell != null)
{
switch (excelSheetGetRowGetCell.CellType)
{
case CellType.String:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue);
break;
case CellType.Numeric:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue);
break;
case CellType.Boolean:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue);
break;
case CellType.Blank:
excelSheetGetRowGetCellSample.SetCellValue(string.Empty);
break;
case CellType.Error:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue);
break;
case CellType.Formula:
{
switch (excelSheetGetRowGetCellSample.CachedFormulaResultType)
{
case CellType.Numeric:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue);
break;
case CellType.String:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue);
break;
case CellType.Boolean:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue);
break;
case CellType.Error:
excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue);
break;
}
}
break;
case CellType.Unknown:
excelSheetGetRowGetCellSample.SetCellValue("Unknown");
break;
}
}
}
else //if (c > sheetDementionSample.FirstCol)
{
if (r == sheetDementionSample.FirstRow)
{
excelSheetGetRowGetCellSample.SetCellValue("Heading");
}
else if (r == sheetDementionSample.FirstRow + 1)
{
excelSheetGetRowGetCellSample.SetCellValue("Schema");
}
else
{
excelSheetGetRowGetCellSample.SetCellValue(string.Empty);
}
}
}
}
catch { }
}
}
}
FileStream fs = new FileStream(mixExcelSample.PathFile, FileMode.OpenOrCreate);
hssWorkbookSample.Write(fs);
_stream = fs;
#endregion
}
if (_stream != null)
{
byte[] binaryData = new byte[_stream.Length];
long bytesRead = _stream.Read(binaryData, 0, (int)_stream.Length);
_stream.Close();
result = Convert.ToBase64String(binaryData);
}
mixExcelSample.CloseStream();
return(result);
}
/// <summary>
/// 创建数据行
/// </summary>
/// <param name="sheet"></param>
private void GenerateDataRow(HSSFWorkbook workbook, HSSFSheet sheet)
{
for (var i = 1; i <= dataList.Count; i++)
{
var row = (HSSFRow) sheet.GetRow(i);
Model.Database.DbBean dbBean = dataList[i - 1];
//序号
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(i);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellStyle(workbook, cell0);
//设备类型
cell0 = row.CreateCell(1);
cell0.SetCellValue(dbBean.DeviceType);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellStyle(workbook, cell0);
//公里标
cell0 = row.CreateCell(2);
cell0.SetCellValue(dbBean.KilometerMark);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellStyle(workbook, cell0);
//侧向
cell0 = row.CreateCell(3);
cell0.SetCellValue(dbBean.SideDirection);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellStyle(workbook, cell0);
//距线路中心距离
cell0 = row.CreateCell(4);
cell0.SetCellValue(dbBean.DistanceToRail);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellStyle(workbook, cell0);
//经度
double temp;
if (double.TryParse(dbBean.Longitude, out temp))
{
cell0 = row.CreateCell(5);
cell0.SetCellValue(Double.Parse((dbBean.Longitude)));
cell0.CellStyle.ShrinkToFit = true;
InitDataCellNumStyle(workbook, cell0);
}
else
{
cell0 = row.CreateCell(5);
cell0.SetCellValue((dbBean.Longitude));
cell0.CellStyle.ShrinkToFit = true;
InitDataCellNumStyle(workbook, cell0);
}
//纬度
if (double.TryParse(dbBean.Latitude, out temp))
{
cell0 = row.CreateCell(6);
cell0.SetCellValue(double.Parse(dbBean.Latitude));
cell0.CellStyle.ShrinkToFit = true;
InitDataCellNumStyle(workbook, cell0);
}
else
{
cell0 = row.CreateCell(6);
cell0.SetCellValue(dbBean.Latitude);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellNumStyle(workbook, cell0);
}
//备注文本
cell0 = row.CreateCell(7);
cell0.SetCellValue(dbBean.Comment);
cell0.CellStyle.ShrinkToFit = true;
InitDataCellStyle(workbook, cell0);
}
}
/// <summary>
/// 创建Excel的表头
/// </summary>
private void GenerateHeaderRow(HSSFWorkbook workbook, HSSFSheet sheet)
{
//获取第一行
HSSFRow row = (HSSFRow) sheet.GetRow(0);
string[] columns = {"序号", "设备类型", "公里标", "侧向", "距线路中心距离", "经度", "纬度", "备注文本"};
for (int i = 0; i<columns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(columns[i]);
//cell.CellStyle.ShrinkToFit = true;
InitHeaderCellStyle(workbook, cell);
}
}
/// <summary>
/// Gets the row height in points.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="rowNum">The row num.</param>
/// <returns></returns>
private float GetRowHeightInPoints(HSSFSheet sheet, int rowNum)
{
HSSFRow row = sheet.GetRow(rowNum);
if (row == null)
return sheet.DefaultRowHeightInPoints;
else
return row.HeightInPoints;
}
// IEnumerable<TestData> data = db.TestDatas;
public static void populateResponseGoalData(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<Scenario> data)
{
//set headerRow and 1st column
const int maxRows = 65536; //npoi uses excel 2003
int hRowNum = 4; //row starts at 0.
int startCol = 0;
HSSFRow headerRow = sheet.GetRow(hRowNum);
int colIndex = startCol;
int errorRow = 2; //note errors
int errorCol = 6; //note errors
#region headers
//date
sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);
//Title
sheet.GetRow(1).GetCell(1).SetCellValue(data.FirstOrDefault().Project.Name);
// handling headers.
headerRow.GetCell(colIndex).SetCellValue("Priority");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Name");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Votes");
colIndex++;
foreach (var utilityitem in data.FirstOrDefault().Utilities)
{
//write each field
headerRow.GetCell(colIndex).SetCellValue(utilityitem.QualityAttributeResponseType.Type.ToString() + " Response Goal");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue(utilityitem.QualityAttributeResponseType.Type.ToString()+ " Utility");
colIndex++;
// if (utilityitem.Utility1.HasValue)//only top 1/6 have utility value
// {
// headerRow.GetCell(colIndex).SetCellValue(utilityitem.Description.ToString());
// }
// colIndex++;
}// end utility item loop
#endregion
#region populateData
// foreach (DataColumn column in propertyInfos)
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = hRowNum + 1;
try{
foreach (var item in data)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex);
if (rowIndex < maxRows - 1)
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue(item.Priority.ToString());
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Name.ToString());
colIndex++;
dataRow.CreateCell(colIndex).SetCellValue(item.Votes.Value);
colIndex++;
foreach (var utilityitem in item.Utilities)
{
//write each field
dataRow.CreateCell(colIndex).SetCellValue(utilityitem.Description.ToString());
colIndex++;
if (utilityitem.Utility1.HasValue)//only top 1/6 have utility value
{
dataRow.CreateCell(colIndex).SetCellValue(utilityitem.Utility1.Value);
}
colIndex++;
}// end utility item loop
rowIndex++;
}//end if check max rows
#endregion
#region populateErrors
else
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue("Dataset exceeds maximum number of rows");
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Data Exceeds max records");
}
}//end data loop
}// end try
catch
{
colIndex = startCol;
if (sheet.GetRow(errorRow).LastCellNum >= errorCol) //error cell exists
{
sheet.GetRow(errorRow).GetCell(errorCol).SetCellValue("Error Occured");
}
else
{//create cell for error message
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Error Occured");
}
}
#endregion
//Assumes template file has table named "ScenarioTable"
// retrieve the named range
//string sRangeName = "scenarioData"; //range name
//int snamedRangeIdx = wb.GetNameIndex(sRangeName); //range index
//HSSFName sRange = wb.GetNameAt(snamedRangeIdx); //get range
//String sheetName = wb.GetSheetName(wb.ActiveSheetIndex); //sheet name
//String reference = sheetName +"!A6:C25"; //new area reference
//sRange.SetRefersToFormula(reference); //set range to new area reference
}
public ActionResult ExportReportExcel(DateTime selected_date)
{
String company_name = "DANGOTE CEMENT";
List <view_message_dto> _sms_list = TempData["sms_list"] as List <view_message_dto>;
// Opening the Excel template...
FileStream fs =
new FileStream(Server.MapPath(@"\Content\template\reporting_sms_List_Selected.xls"), FileMode.Open, FileAccess.Read);
MemoryStream ms = new MemoryStream();
// Getting the complete workbook...
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
try
{
// Getting the worksheet by its name...
NPOI.HSSF.UserModel.HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("SmsList");
//RO's Name
NPOI.HSSF.UserModel.HSSFRow dataRow = (HSSFRow)sheet.GetRow(3);
ICell cell_default = dataRow.Cells[0];
ICellStyle boldStyle = cell_default.CellStyle;
dataRow = (HSSFRow)sheet.GetRow(0);
dataRow.Cells[0].SetCellValue(company_name);
int Row = 3;
foreach (view_message_dto item in _sms_list)
{
dataRow = (HSSFRow)sheet.CreateRow(Row); //
dataRow.CreateCell(0).SetCellValue(Row - 2);
dataRow.CreateCell(1).SetCellValue(item.atc_no);
dataRow.CreateCell(2).SetCellValue(item.delivery_no);
dataRow.CreateCell(3).SetCellValue(item.customer_no);
dataRow.CreateCell(4).SetCellValue(item.phone_no);
dataRow.CreateCell(5).SetCellValue(item.waybill_no);
dataRow.CreateCell(6).SetCellValue(item.customer_name);
dataRow.CreateCell(7).SetCellValue(item.ip_address);
dataRow.CreateCell(8).SetCellValue(item.code_a);
dataRow.CreateCell(9).SetCellValue(item.code_b);
dataRow.CreateCell(10).SetCellValue(item.sms_status);
dataRow.CreateCell(11).SetCellValue(item.response);
dataRow.CreateCell(12).SetCellValue(item.create_datetime.ToString());
dataRow.CreateCell(13).SetCellValue(item.response_datetime.ToString());
if (Row % 2 == 0)
{
for (int i = 0; i < 5; i++)
{
ICell cell = dataRow.Cells[i];
cell.CellStyle = boldStyle;
}
}
Row++;
}
sheet.ForceFormulaRecalculation = true;
TempData["Message"] = "Excel report created successfully!";
}
catch (Exception ex)
{
TempData["Message"] = "Oops! Something went wrong." + "<br/>" + ex.Message.ToString();
}
String FileName = "sms_list_report_" + selected_date.ToString("-yyyy-MM-dd") + ".xls";
// Writing the workbook content to the FileStream...
templateWorkbook.Write(ms);
return(File(ms.ToArray(), "application/vnd.ms-excel", FileName));
}
/// <summary>
/// 解锁
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="columnIndex"></param>
public static void UnlockColumn(HSSFWorkbook workbook, HSSFSheet sheet, int columnIndex)
{
HSSFCellStyle unlocked = workbook.CreateCellStyle();
unlocked.IsLocked = false;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
sheet.GetRow(i).GetCell(columnIndex).CellStyle = unlocked;
}
}
private void InsertRows(HSSFSheet sheet1, int fromRowIndex, int rowCount)
{
sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false, true);
for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
{
IRow rowSource = sheet1.GetRow(rowIndex + rowCount);
IRow rowInsert = sheet1.CreateRow(rowIndex);
rowInsert.Height = rowSource.Height;
for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
{
ICell cellSource = rowSource.GetCell(colIndex);
ICell cellInsert = rowInsert.CreateCell(colIndex);
if (cellSource != null)
{
cellInsert.CellStyle = cellSource.CellStyle;
}
}
}
}
/// <summary>
/// 解锁
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="columnIndex"></param>
public static void UnlockCell(HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, int columnIndex)
{
HSSFCellStyle unlocked = workbook.CreateCellStyle();
unlocked.IsLocked = false;
sheet.GetRow(rowIndex).GetCell(columnIndex).CellStyle = unlocked;
}
/// <summary>
/// HSSFRow Copy Command
///
/// Description: Inserts a existing row into a new row, will automatically push down
/// any existing rows. Copy is done cell by cell and supports, and the
/// command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNum">Source Row Number</param>
/// <param name="destinationRowNum">Destination Row Number</param>
private HSSFRow CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
var newRow = (HSSFRow)worksheet.GetRow(destinationRowNum);
var sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (var i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
var oldCell = (HSSFCell)sourceRow.GetCell(i);
var newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null) continue;
// Copy style from old cell and apply to new cell
var newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.BLANK:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.ERROR:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.FORMULA:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.STRING:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (var i = 0; i < worksheet.NumMergedRegions; i++)
{
var cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow != sourceRow.RowNum) continue;
var newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
return newRow;
}
/// <summary>
/// 往单元格写数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="columnIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static HSSFCell WriteCell(HSSFSheet sheet, int columnIndex, int rowIndex, object obj)
{
HSSFRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
return WriteCell(row, obj, columnIndex);
}
/// <summary>
/// Créer une Cellule avec un Style en paramètre
/// </summary>
/// <param name="_sheet"></param>
/// <param name="_cellStyle"></param>
/// <param name="_numCol"></param>
/// <param name="_numRow"></param>
/// <param name="_text"></param>
public void createCell(HSSFSheet _sheet, HSSFCellStyle _cellStyle, int _numCol, int _numRow, string _text)
{
HSSFRow dataRow = (HSSFRow)_sheet.GetRow(_numRow);
NPOI.SS.UserModel.ICell _newCell = dataRow.CreateCell(_numCol);
_newCell.SetCellValue(_text);
_newCell.CellStyle = _cellStyle;
}
/// <summary>
/// 在指定位置插入图片，跨Column。ColumnIndex2需大于或等于ColumnIndex1
/// </summary>
public static void WirtePic(HSSFWorkbook workbook, HSSFSheet sheet, HSSFPatriarch patriarch, int columnIndex1, int columnIndex2, int rowIndex, string picPath)
{
if (!File.Exists(picPath))
{
return;
}
if (columnIndex2 < columnIndex1)
{
throw new Exception("ColumnIndex2需大于或等于ColumnIndex1");
}
int dx2 = 1023;
int dy2 = 255;
HSSFRow row = sheet.GetRow(rowIndex);
if (row != null)
{
int cWidth = 0;
for (int i = columnIndex1; i < columnIndex2; i++)
{
cWidth += sheet.GetColumnWidth(columnIndex1);
}
using (Image img = Image.FromFile(picPath))
{
double w = cWidth * 7.0 / 256.0;
double h = (row.Height * 1.32 / 20.0);
if (((double)img.Width / (double)img.Height) > (cWidth * 7.0 / 256 / (row.Height * 1.32 / 20)))
{
double h1 = w * img.Height / img.Width;
dy2 = (int)(h1 * 255 / h);
if (dy2 < 0) dy2 = 0;
if (dy2 > 255) dy2 = 255;
}
else
{
double w1 = h * img.Width / img.Height;
dx2 = (int)(w1 * 1023 / w);
if (dy2 < 0) dy2 = 0;
if (dy2 > 1023) dy2 = 1023;
}
}
}
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, dx2, dy2, columnIndex1, rowIndex, columnIndex2, rowIndex);
byte[] buff = File.ReadAllBytes(picPath);
int pic = workbook.AddPicture(buff, HSSFWorkbook.PICTURE_TYPE_JPEG);
anchor.AnchorType = 2;
patriarch.CreatePicture(anchor, pic);
}
protected void ProcessSheet(HSSFSheet sheet)
{
ProcessSheetHeader(htmlDocumentFacade.Body, sheet);
int physicalNumberOfRows = sheet.PhysicalNumberOfRows;
if (physicalNumberOfRows <= 0)
return;
XmlElement table = htmlDocumentFacade.CreateTable();
table.SetAttribute("class", cssClassTable);
XmlElement tableBody = htmlDocumentFacade.CreateTableBody();
CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.BuildMergedRangesMap(sheet);
List<XmlElement> emptyRowElements = new List<XmlElement>(physicalNumberOfRows);
int maxSheetColumns = 1;
for (int r = 0; r < physicalNumberOfRows; r++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(r);
if (row == null)
continue;
if (!OutputHiddenRows && row.ZeroHeight)
continue;
XmlElement tableRowElement = htmlDocumentFacade.CreateTableRow();
htmlDocumentFacade.AddStyleClass(tableRowElement, "r", "height:"
+ (row.Height / 20f) + "pt;");
int maxRowColumnNumber = ProcessRow(mergedRanges, row,
tableRowElement);
if (maxRowColumnNumber == 0)
{
emptyRowElements.Add(tableRowElement);
}
else
{
if (emptyRowElements.Count > 0)
{
foreach (XmlElement emptyRowElement in emptyRowElements)
{
tableBody.AppendChild(emptyRowElement);
}
emptyRowElements.Clear();
}
tableBody.AppendChild(tableRowElement);
}
maxSheetColumns = Math.Max(maxSheetColumns, maxRowColumnNumber);
}
ProcessColumnWidths(sheet, maxSheetColumns, table);
if (OutputColumnHeaders)
{
ProcessColumnHeaders(sheet, maxSheetColumns, table);
}
table.AppendChild(tableBody);
htmlDocumentFacade.Body.AppendChild(table);
}
/// <summary>
///
/// </summary>
/// <param name="sheet1"></param>
/// <param name="dt"></param>
/// <param name="dtPrevYear"></param>
/// <param name="CurrentMonth"></param>
/// <param name="DepId"></param>
private void FillExcelPersonProfit(HSSFSheet sheet1, DataTable dt, DataTable dtPrevYear,
int CurrentMonth, string DepId,int Cnt, int PrevCnt)
{
switch (CurrentMonth)
{
case 1:
sheet1.Items(0, 1).SetCellValue("Dec");
sheet1.Items(0, 2).SetCellValue("Jan");
sheet1.Items(0, 3).SetCellValue("Total(Dec+Jan)");
break;
case 2:
sheet1.Items(0, 1).SetCellValue("Jan");
sheet1.Items(0, 2).SetCellValue("Feb");
sheet1.Items(0, 3).SetCellValue("Total(Jan+Feb)");
break;
case 3:
sheet1.Items(0, 1).SetCellValue("Feb");
sheet1.Items(0, 2).SetCellValue("Mar");
sheet1.Items(0, 3).SetCellValue("Total(Feb+Mar)");
break;
case 4:
sheet1.Items(0, 1).SetCellValue("Mar");
sheet1.Items(0, 2).SetCellValue("Apr");
sheet1.Items(0, 3).SetCellValue("Total(Mar+Apr)");
break;
case 5:
sheet1.Items(0, 1).SetCellValue("Apr");
sheet1.Items(0, 2).SetCellValue("May");
sheet1.Items(0, 3).SetCellValue("Total(Apr+May)");
break;
case 6:
sheet1.Items(0, 1).SetCellValue("May");
sheet1.Items(0, 2).SetCellValue("Jun");
sheet1.Items(0, 3).SetCellValue("Total(May+Jun)");
break;
case 7:
sheet1.Items(0, 1).SetCellValue("Jun");
sheet1.Items(0, 2).SetCellValue("Jul");
sheet1.Items(0, 3).SetCellValue("Total(Jun+Jul)");
break;
case 8:
sheet1.Items(0, 1).SetCellValue("Jul");
sheet1.Items(0, 2).SetCellValue("Aug");
sheet1.Items(0, 3).SetCellValue("Total(Jul+Aug)");
break;
case 9:
sheet1.Items(0, 1).SetCellValue("Aug");
sheet1.Items(0, 2).SetCellValue("Sep");
sheet1.Items(0, 3).SetCellValue("Total(Aug+Sep)");
break;
case 10:
sheet1.Items(0, 1).SetCellValue("Sep");
sheet1.Items(0, 2).SetCellValue("Oct");
sheet1.Items(0, 3).SetCellValue("Total(Sep+Oct)");
break;
case 11:
sheet1.Items(0, 1).SetCellValue("Oct");
sheet1.Items(0, 2).SetCellValue("Nov");
sheet1.Items(0, 3).SetCellValue("Total(Oct+Nov)");
break;
case 12:
sheet1.Items(0, 1).SetCellValue("Nov");
sheet1.Items(0, 2).SetCellValue("Dec");
sheet1.Items(0, 3).SetCellValue("Total(Nov+Dec)");
break;
default:
break;
}
//是否按部门过滤数据
DataRow[] DRS =new DataRow[dt.Rows.Count];
DataRow[] DRSPrevYear = new DataRow[dtPrevYear.Rows.Count];
if (DepId != "")
{
DRS = dt.Select("DepId=" + DepId);
//只有1月份的时候，才有去年的数值
if(dtPrevYear.Rows.Count>0)
DRSPrevYear = dtPrevYear.Select("DepId=" + DepId);
}
else
{
dt.Rows.CopyTo(DRS, 0);
dtPrevYear.Rows.CopyTo(DRSPrevYear, 0);
}
//row,cell都是从0开始计数
//第1行title,不是数据
for (int i = 0; i < DRS.Length; i++) //每个员工
{
DataRow dr = DRS[i];
HSSFCell cell = sheet1.Items((i + 1), 0);
cell.SetCellValue(dr[1].ToString()); //员工名字
//dr[5~16]的值是该员工1-12月的profit 1=>5,3=>7,5=>9,7=>11,9=>13,11=>15
double monthProfit1, monthProfit2;
if (CurrentMonth == 1)
{
monthProfit1 = double.Parse(DRSPrevYear[i][12+4].ToString());
monthProfit2 = double.Parse(dr[CurrentMonth + 4].ToString());
}
else
{
monthProfit1 = double.Parse(dr[CurrentMonth + 3].ToString());
monthProfit2 = double.Parse(dr[CurrentMonth + 4].ToString());
}
sheet1.Items((i + 1), 1).SetCellValue(monthProfit1);
sheet1.Items((i + 1), 2).SetCellValue(monthProfit2);
}
//模板员工行数现在是23行,假如超过就要再加
for (int i = DRS.Length+1; i < 23; i++)
{
sheet1.GetRow(i).Hide();
}
//28B,28C是出票数量
sheet1.Items(27, 1).SetCellValue(PrevCnt);
sheet1.Items(27, 2).SetCellValue(Cnt);
}
/// <summary>
/// 插入Excel行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
/// <param name="row"></param>
private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row)
{
#region 批量移动行
sheet.ShiftRows(
rowIndex, //--开始行
sheet
.LastRowNum, //--结束行
count, //--移动大小(行数)--往下移动
true, //是否复制行高
false, //是否重置行高
true //是否移动批注
);
#endregion
#region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
for (int i = rowIndex; i < rowIndex + count - 1; i++)
{
HSSFRow targetRow;
HSSFCell sourceCell;
HSSFCell targetCell;
targetRow = sheet.CreateRow(i + 1);
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
sourceCell = row.GetCell(m);
if (sourceCell == null)
continue;
targetCell = targetRow.CreateCell(m);
targetCell.Encoding = sourceCell.Encoding;
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
//CopyRow(sourceRow, targetRow);
//Util.CopyRow(sheet, sourceRow, targetRow);
}
HSSFRow firstTargetRow = sheet.GetRow(rowIndex);
HSSFCell firstSourceCell;
HSSFCell firstTargetCell = null;
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
firstSourceCell = row.GetCell(m);
if (firstSourceCell == null)
continue;
firstTargetCell = firstTargetRow.CreateCell(m);
firstTargetCell.Encoding = firstSourceCell.Encoding;
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4));
#endregion
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <returns></returns>
static DataTable ImportDt(HSSFSheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable table = new DataTable();
HSSFRow headerRow;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
HSSFRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as HSSFRow;
}
else
{
row = sheet.GetRow(i) as HSSFRow;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.STRING:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.FORMULA:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.STRING:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
return table;
}
/// <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 HSSFRow GetRow(int rowCounter, HSSFSheet sheet)
{
HSSFRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return row;
}
/// <summary>
/// Créer une Cellule avec un Style en paramètre
/// </summary>
/// <param name="_sheet"></param>
/// <param name="_cellStyle"></param>
/// <param name="_numCol"></param>
/// <param name="_numRow"></param>
/// <param name="_text"></param>
public void createCell(HSSFSheet _sheet, HSSFCellStyle _cellStyle, int _numCol, int _numRow, decimal _value, string _currency)
{
HSSFRow dataRow = (HSSFRow)_sheet.GetRow(_numRow);
NPOI.SS.UserModel.ICell _newCell = dataRow.CreateCell(_numCol);
//_cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0,00 " + _currency);
_newCell.SetCellValue((double)_value);
_newCell.CellStyle = _cellStyle;
}
/// <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.IRow GetRow(int rowCounter, HSSFSheet sheet)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowCounter);
if (row == null)
{
row = sheet.CreateRow(rowCounter);
}
return row;
}
public static void populateStrategyBenefitSummaryData(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<Benefit> data)
{
//set headerRow and 1st column
const int maxRows = 65535; //npoi uses excel 2003
int hRowNum = 4; //row starts at 0.
int startCol = 1; //col starts at 0
int errorRow = 2; //note errors
int errorCol = 6; //note errors
HSSFRow headerRow = sheet.GetRow(hRowNum);
int colIndex = startCol;
#region headers
//date
sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);
//Title
sheet.GetRow(1).GetCell(colIndex).SetCellValue(data.FirstOrDefault().ProjectName);
// handling headers.
headerRow.GetCell(colIndex).SetCellValue("Rank");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Strategy");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("ID");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Cost");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("Benefit");
colIndex++;
headerRow.GetCell(colIndex).SetCellValue("ROI");
colIndex++;
#endregion
#region populateData
// foreach (DataColumn column in propertyInfos)
// headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = hRowNum + 1;
try
{
foreach (var item in data)
{
HSSFRow dataRow = sheet.GetRow(rowIndex);
if (rowIndex < maxRows - 1)
{
//write each field
colIndex = startCol;
dataRow.GetCell(colIndex).SetCellValue(rowIndex - hRowNum); //rank = rec#
colIndex++;
dataRow.GetCell(colIndex).SetCellValue(item.StrategyName.ToString());
colIndex++;
dataRow.GetCell(colIndex).SetCellValue(item.StrategyID.ToString());
colIndex++;
dataRow.GetCell(colIndex).SetCellValue(item.StrategyCost.Value);
colIndex++;
dataRow.GetCell(colIndex).SetCellValue(item.Benefit1.Value);
colIndex++;
rowIndex++;
}//end if check max rows
#endregion
#region errors
else
{
colIndex = startCol;
dataRow.CreateCell(colIndex).SetCellValue("Dataset exceeds maximum number of rows");
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Data Exceeds max records");
}
}//end data loop
}// end try
catch
{
colIndex = startCol;
if (sheet.GetRow(errorRow).LastCellNum >= errorCol) //error cell exists
{
sheet.GetRow(errorRow).GetCell(errorCol).SetCellValue("Error Occured");
}else
{//create cell for error message
sheet.GetRow(errorRow).CreateCell(errorCol).SetCellValue("Error Occured");
}
}
#endregion
}
