//[Obsolete]
//public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderBottom(border, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the borderBottom attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="border">The new border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.LastRow;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_BOTTOM, (int)border);
NPOI.SS.UserModel.Row row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
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>()));
}
}
protected void GSD_btn_Click(object sender, EventArgs e)
{
try
{
this.workbook = new HSSFWorkbook(PYS_FileUpload.FileContent);
this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0); //取得第0個Sheet
//不同於Microsoft Object Model，NPOI都是從索引0開始算起
//從第一個Worksheet讀資料
SaveOrInsertSheet(this.u_sheet);
ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true);
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入失敗", "alert('匯入失敗');", true);
}
finally
{
//釋放 NPOI的資源
if (this.workbook != null) this.workbook = null;
if (this.u_sheet != null) this.u_sheet = null;
//是否刪除Server上的Excel檔(預設true)
/*bool isDeleteFileFromServer = false;
if (isDeleteFileFromServer)
{
System.IO.File.Delete(excel_filePath);
}*/
GC.Collect();
}
}
private ExcelSheet ResolveSheet(Npoi.HSSFSheet native)
{
if (!_sheets.ContainsKey(native))
{
_sheets.Add(native, new NpoiExcelSheet(native));
}
return(_sheets[native]);
}
/// <summary>
/// Creates new HSSFRow from scratch. Only HSSFSheet should do this.
/// </summary>
/// <param name="book">low-level Workbook object containing the sheet that Contains this row</param>
/// <param name="sheet">low-level Sheet object that Contains this Row</param>
/// <param name="rowNum">the row number of this row (0 based)</param>
///<see cref="NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/>
public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, int rowNum)
{
this.rowNum = rowNum;
this.book = book;
this.sheet = sheet;
row = new RowRecord(rowNum);
RowNum=(rowNum);
}
/// <summary>
/// Creates the patriarch.
/// </summary>
/// <param name="sheet">the sheet this patriarch is stored in.</param>
/// <param name="boundAggregate">The bound aggregate.</param>
public HSSFPatriarch(HSSFSheet sheet, EscherAggregate boundAggregate)
{
_boundAggregate = boundAggregate;
_sheet = sheet;
_mainSpgrContainer = _boundAggregate.GetEscherContainer().ChildContainers[0];
EscherContainerRecord spContainer = (EscherContainerRecord)_boundAggregate.GetEscherContainer()
.ChildContainers[0].GetChild(0);
_spgrRecord = (EscherSpgrRecord)spContainer.GetChildById(EscherSpgrRecord.RECORD_ID);
BuildShapeTree();
}
private void CreateFilterModeRecord(HSSFSheet sheet,int insertPos)
{
//look for the FilterModeRecord
NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(FilterModeRecord.sid);
// this local variable hides the class one: FilterModeRecord filtermode;
//if not found, add a new one
if (record == null)
{
filtermode = new FilterModeRecord();
sheet.Sheet.Records.Insert(insertPos, filtermode);
}
}
public CellEvaluationFrame(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
{
if (workbook == null)
{
throw new ArgumentException("workbook must not be null");
}
if (sheet == null)
{
throw new ArgumentException("sheet must not be null");
}
_workbook = workbook;
_sheet = sheet;
_srcRowNum = srcRowNum;
_srcColNum = srcColNum;
}
public ExcalHelper(String sheetName, String[] rowTitle, int[] rowWidth, int dataRowNum)
{
//创建标题行
workbook = new HSSFWorkbook();
sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
this.dataRowNum = dataRowNum;
HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < rowTitle.Length; i++)
{
//设置列宽
sheet.SetColumnWidth(i, rowWidth[i] * 255);
rowtitle.CreateCell(i).SetCellValue(rowTitle[i]);
}
}
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg)
{
//look for the AutoFilterInfo Record
NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid);
AutoFilterInfoRecord info;
if (record == null)
{
info = new AutoFilterInfoRecord();
sheet.Sheet.Records.Insert(insertPos, info);
}
else
{
info = record as AutoFilterInfoRecord;
}
info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1);
}
/**
* Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast
* retrieving {@link CellRangeAddress} of any cell, if cell is contained in
* range.
*
* @see #getMergedRange(CellRangeAddress[][], int, int)
*/
public static CellRangeAddress[][] BuildMergedRangesMap(HSSFSheet sheet)
{
CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][];
for ( int m = 0; m < sheet.NumMergedRegions; m++ )
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion( m );
int requiredHeight = cellRangeAddress.LastRow + 1;
if ( mergedRanges.Length < requiredHeight )
{
CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][];
Array.Copy( mergedRanges, 0, newArray, 0, mergedRanges.Length );
mergedRanges = newArray;
}
for ( int r = cellRangeAddress.FirstRow; r <= cellRangeAddress.LastRow; r++ )
{
int requiredWidth = cellRangeAddress.LastColumn + 1;
CellRangeAddress[] rowMerged = mergedRanges[r];
if ( rowMerged == null )
{
rowMerged = new CellRangeAddress[requiredWidth];
mergedRanges[r] = rowMerged;
}
else
{
int rowMergedLength = rowMerged.Length;
if ( rowMergedLength < requiredWidth )
{
CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth];
Array.Copy(rowMerged, 0, newRow, 0,rowMergedLength );
mergedRanges[r] = newRow;
rowMerged = newRow;
}
}
//Arrays.Fill( rowMerged, cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn + 1, cellRangeAddress );
for (int i = cellRangeAddress.FirstColumn; i < cellRangeAddress.LastColumn + 1; i++)
{
rowMerged[i] = cellRangeAddress;
}
}
}
return mergedRanges;
}
public static HSSFPatriarch CreatePatriarch(HSSFPatriarch patriarch, HSSFSheet sheet)
{
HSSFPatriarch newPatriarch = new HSSFPatriarch(sheet, new EscherAggregate(true));
newPatriarch.AfterCreate();
foreach (HSSFShape shape in patriarch.Children)
{
HSSFShape newShape;
if (shape is HSSFShapeGroup)
{
newShape = ((HSSFShapeGroup)shape).CloneShape(newPatriarch);
}
else
{
newShape = shape.CloneShape();
}
newPatriarch.OnCreate(newShape);
newPatriarch.AddShape(newShape);
}
return newPatriarch;
}
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;
}
}
/**
* Notifies this evaluation tracker that the evaluation of the specified
* cell Is complete. <p/>
*
* Every successful call to <tt>startEvaluate</tt> must be followed by a
* call to <tt>endEvaluate</tt> (recommended in a finally block) to enable
* proper tracking of which cells are being evaluated at any point in time.<p/>
*
* Assuming a well behaved client, parameters to this method would not be
* required. However, they have been included to assert correct behaviour,
* and form more meaningful error messages.
*/
public void EndEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
{
int nFrames = _evaluationFrames.Count;
if (nFrames < 1)
{
throw new InvalidOperationException("Call to endEvaluate without matching call to startEvaluate");
}
nFrames--;
CellEvaluationFrame cefExpected = (CellEvaluationFrame)_evaluationFrames[nFrames];
CellEvaluationFrame cefActual = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum);
if (!cefActual.Equals(cefExpected))
{
throw new Exception("Wrong cell specified. "
+ "Corresponding startEvaluate() call was for cell {"
+ cefExpected.FormatAsString() + "} this endEvaluate() call Is for cell {"
+ cefActual.FormatAsString() + "}");
}
// else - no problems so pop current frame
_evaluationFrames.Remove(nFrames);
}
/// <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);
}
/**
* @return aggregate info or null if the sheet does not contain Drawing objects
*/
internal static DrawingAggregateInfo Get(HSSFSheet sheet)
{
DrawingAggregateInfo info = null;
InternalSheet isheet = HSSFTestHelper.GetSheetForTest(sheet);
List<RecordBase> records = isheet.Records;
for (int i = 0; i < records.Count; i++)
{
RecordBase rb = records[(i)];
if ((rb is DrawingRecord) && info == null)
{
info = new DrawingAggregateInfo();
info.startRecordIndex = i;
info.endRecordIndex = i;
}
else if (info != null && (
rb is DrawingRecord
|| rb is ObjRecord
|| rb is TextObjectRecord
|| rb is ContinueRecord
|| rb is NoteRecord
))
{
info.endRecordIndex = i;
}
else
{
if (rb is EscherAggregate)
throw new InvalidOperationException("Drawing data already aggregated. " +
"You should cal this method before the first invocation of HSSFSheet#getDrawingPatriarch()");
if (info != null) break;
}
}
if (info != null)
{
info.aggRecords = new List<RecordBase>(
records.GetRange(info.startRecordIndex, info.endRecordIndex + 1));
}
return info;
}
protected void Source_btn_Click(object sender, EventArgs e)
{
//try
//{
this.workbook = new HSSFWorkbook(Source_FileUpload.FileContent);
this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0); //取得第0個Sheet
//不同於Microsoft Object Model，NPOI都是從索引0開始算起
//從第一個Worksheet讀資料
SaveOrInsertSheet_Source(this.u_sheet);
ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true);
//}
//catch (Exception)
//{
// ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入失敗", "alert('匯入失敗');", true);
//}
//finally
//{
//釋放 NPOI的資源
if (this.workbook != null) this.workbook = null;
if (this.u_sheet != null) this.u_sheet = null;
GC.Collect();
//}
}
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;
}
}
/// <summary>
/// Calculates the height of a client anchor in points.
/// </summary>
/// <param name="sheet">the sheet the anchor will be attached to</param>
/// <returns>the shape height.</returns>
public float GetAnchorHeightInPoints(HSSFSheet sheet)
{
int y1 = Dy1;
int y2 = Dy2;
int row1 = Math.Min(Row1, Row2);
int row2 = Math.Max(Row1, Row2);
float points = 0;
if (row1 == row2)
{
points = ((y2 - y1) / 256.0f) * GetRowHeightInPoints(sheet, row2);
}
else
{
points += ((256.0f - y1) / 256.0f) * GetRowHeightInPoints(sheet, row1);
for (int i = row1 + 1; i < row2; i++)
{
points += GetRowHeightInPoints(sheet, i);
}
points += (y2 / 256.0f) * GetRowHeightInPoints(sheet, row2);
}
return points;
}
/// <summary>Get a worksheet by name.</summary>
/// <param name="name">Name of the desired sheet.</param>
/// <returns>A facade representing the sheet.</returns>
public override ExcelSheet GetSheet(string name)
{
Npoi.HSSFSheet native = (Npoi.HSSFSheet) this._workbook.GetSheet(name);
return(ResolveSheet(native));
}
/**
* Returns all the charts for the given sheet.
*
* NOTE: You won't be able to do very much with
* these charts yet, as this is very limited support
*/
public static HSSFChart[] GetSheetCharts(HSSFSheet sheet)
{
List<HSSFChart> charts = new List<HSSFChart>();
HSSFChart lastChart = null;
HSSFSeries lastSeries = null;
// Find records of interest
IList records = sheet.Sheet.Records;
foreach (RecordBase r in records)
{
if (r is ChartRecord)
{
lastSeries = null;
lastChart = new HSSFChart(sheet, (ChartRecord)r);
charts.Add(lastChart);
}
else if (r is LegendRecord)
{
lastChart.legendRecord = (LegendRecord)r;
}
else if (r is SeriesRecord)
{
HSSFSeries series = new HSSFSeries((SeriesRecord)r);
lastChart.series.Add(series);
lastSeries = series;
}
else if (r is AlRunsRecord)
{
lastChart.chartTitleFormat =
(AlRunsRecord)r;
}
else if (r is SeriesTextRecord)
{
// Applies to a series, unless we've seen
// a legend already
SeriesTextRecord str = (SeriesTextRecord)r;
if (lastChart.legendRecord == null &&
lastChart.series.Count > 0)
{
HSSFSeries series = (HSSFSeries)
lastChart.series[lastChart.series.Count - 1];
series.seriesTitleText = str;
}
else
{
lastChart.chartTitleText = str;
}
}
else if (r is BRAIRecord)
{
BRAIRecord linkedDataRecord = (BRAIRecord)r;
if (lastSeries != null)
{
lastSeries.InsertData(linkedDataRecord);
}
}
else if (r is ValueRangeRecord)
{
lastChart.valueRanges.Add((ValueRangeRecord)r);
}
else if (r is Record)
{
if (lastChart != null)
{
Record record = (Record)r;
foreach (int type in Enum.GetValues(typeof(HSSFChartType)))
{
if (type == 0)
{
continue;
}
if (record.Sid == type)
{
lastChart.type = (HSSFChartType)type;
break;
}
}
}
}
}
return (HSSFChart[])
charts.ToArray();
}
/**
* TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
* this call will produce the wrong results if the formula contains any cell references
* One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int)
* Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
*
* @return <code>null</code> if <c>formula</c> was null.
*/
private static Ptg[] ParseFormula(String formula, HSSFSheet sheet)
{
if (formula == null)
{
return null;
}
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
return HSSFFormulaParser.Parse(formula, (HSSFWorkbook)sheet.Workbook, FormulaType.Cell, sheetIndex);
}
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));
}
public static CFRuleRecord Create(HSSFSheet sheet, String formulaText)
{
Ptg[] formula1 = ParseFormula(formulaText, sheet);
return new CFRuleRecord(CONDITION_TYPE_FORMULA, ComparisonOperator.NoComparison,
formula1, null);
}
/**
* Creates a new comparison operation rule
*/
public static CFRuleRecord Create(HSSFSheet sheet, byte comparisonOperation,
String formulaText1, String formulaText2)
{
Ptg[] formula1 = ParseFormula(formulaText1, sheet);
Ptg[] formula2 = ParseFormula(formulaText2, sheet);
return new CFRuleRecord(CONDITION_TYPE_CELL_VALUE_IS, (ComparisonOperator)comparisonOperation, formula1, formula2);
}
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);
}
private void RemoveFilterModeRecord(HSSFSheet sheet)
{
if(filtermode!=null)
sheet.Sheet.Records.Remove(filtermode);
}
public HSSFAutoFilter(HSSFSheet sheet)
{
_sheet = sheet;
}
/// <summary>Get the sheet at the specific index.</summary>
/// <param name="index">0-based index of the sheet.</param>
/// <returns>A facade representing the sheet.</returns>
public override ExcelSheet GetSheet(int index)
{
Npoi.HSSFSheet native = (Npoi.HSSFSheet) this._workbook.GetSheetAt(index);
return(ResolveSheet(native));
}
private HSSFChart(HSSFSheet sheet, ChartRecord chartRecord)
{
this.chartRecord = chartRecord;
this.sheet = sheet;
}
/**
* Creates a bar chart. API needs some work. :)
*
* NOTE: Does not yet work... checking it in just so others
* can take a look.
*/
public void CreateBarChart(HSSFWorkbook workbook, HSSFSheet sheet)
{
List<RecordBase> records = new List<RecordBase>();
records.Add(CreateMSDrawingObjectRecord());
records.Add(CreateOBJRecord());
records.Add(CreateBOFRecord());
records.Add(new HeaderRecord(string.Empty));
records.Add(new FooterRecord(string.Empty));
records.Add(CreateHCenterRecord());
records.Add(CreateVCenterRecord());
records.Add(CreatePrintSetupRecord());
// unknown 33
records.Add(CreateFontBasisRecord1());
records.Add(CreateFontBasisRecord2());
records.Add(new ProtectRecord(false));
records.Add(CreateUnitsRecord());
records.Add(CreateChartRecord(0, 0, 30434904, 19031616));
records.Add(CreateBeginRecord());
records.Add(CreateSCLRecord((short)1, (short)1));
records.Add(CreatePlotGrowthRecord(65536, 65536));
records.Add(CreateFrameRecord1());
records.Add(CreateBeginRecord());
records.Add(CreateLineFormatRecord(true));
records.Add(CreateAreaFormatRecord1());
records.Add(CreateEndRecord());
records.Add(CreateSeriesRecord());
records.Add(CreateBeginRecord());
records.Add(CreateTitleLinkedDataRecord());
records.Add(CreateValuesLinkedDataRecord());
records.Add(CreateCategoriesLinkedDataRecord());
records.Add(CreateDataFormatRecord());
// records.Add(CreateBeginRecord());
// unknown
// records.Add(CreateEndRecord());
records.Add(new SerToCrtRecord());
records.Add(CreateEndRecord());
records.Add(CreateSheetPropsRecord());
records.Add(CreateDefaultTextRecord((short)TextFormatInfo.FontScaleNotSet));
records.Add(CreateAllTextRecord());
records.Add(CreateBeginRecord());
// unknown
records.Add(CreateFontIndexRecord(5));
records.Add(CreateDirectLinkRecord());
records.Add(CreateEndRecord());
records.Add(CreateDefaultTextRecord((short)3)); // eek, undocumented text type
records.Add(CreateUnknownTextRecord());
records.Add(CreateBeginRecord());
records.Add(CreateFontIndexRecord((short)6));
records.Add(CreateDirectLinkRecord());
records.Add(CreateEndRecord());
records.Add(CreateAxisUsedRecord((short)1));
CreateAxisRecords(records);
records.Add(CreateEndRecord());
records.Add(CreateDimensionsRecord());
records.Add(CreateSeriesIndexRecord(2));
records.Add(CreateSeriesIndexRecord(1));
records.Add(CreateSeriesIndexRecord(3));
records.Add(EOFRecord.instance);
sheet.InsertChartRecords(records);
workbook.InsertChartRecord();
}
public static CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
if (rowNum >= merged.FirstRow && rowNum <= merged.LastRow)
{
if (cellNum >= merged.FirstColumn && cellNum <= merged.LastColumn)
{
return merged;
}
}
}
return null;
}
/// <summary>Default Constructor</summary>
/// <param name="sheet">The native XLSX sheet</param>
public NpoiExcelSheet(Npoi.HSSFSheet sheet)
{
this._sheet = sheet;
}
private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, bool keepFormulas)
{
List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions;
destRow.Height = srcRow.Height;
destRow.IsHidden = srcRow.IsHidden;
destRow.RowRecord.OptionFlags = srcRow.RowRecord.OptionFlags;
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j);
HSSFCell newCell = (HSSFCell)destRow.GetCell(j);
if (srcSheet.Workbook == destSheet.Workbook)
{
newCell = (HSSFCell)destRow.GetCell(j);
}
if (oldCell != null)
{
if (newCell == null)
{
newCell = (HSSFCell)destRow.CreateCell(j);
}
HSSFCellUtil.CopyCell(oldCell, newCell, styleMap, paletteMap, keepFormulas);
CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
}
}
}
}
}
/// <summary>
/// Clones the _sheet.
/// </summary>
/// <param name="workbook">The _workbook.</param>
/// <returns>the cloned sheet</returns>
public ISheet CloneSheet(HSSFWorkbook workbook)
{
IDrawing iDrawing = this.DrawingPatriarch;/*Aggregate drawing records*/
HSSFSheet sheet = new HSSFSheet(workbook, _sheet.CloneSheet());
int pos = sheet._sheet.FindFirstRecordLocBySid(DrawingRecord.sid);
DrawingRecord dr = (DrawingRecord)sheet._sheet.FindFirstRecordBySid(DrawingRecord.sid);
if (null != dr)
{
sheet._sheet.Records.Remove(dr);
}
if (DrawingPatriarch != null)
{
HSSFPatriarch patr = HSSFPatriarch.CreatePatriarch(this.DrawingPatriarch as HSSFPatriarch, sheet);
sheet._sheet.Records.Insert(pos, patr.GetBoundAggregate());
sheet._patriarch = patr;
}
return sheet;
}
public HSSFEvaluationSheet(HSSFSheet hs)
{
_hs = hs;
}
/// <summary>
/// HSSFSheet清除指定区域的图片
/// </summary>
/// <param name="sheet"></param>
/// <param name="minRow"></param>
/// <param name="maxRow"></param>
/// <param name="minCol"></param>
/// <param name="maxCol"></param>
/// <param name="onlyInternal"></param>
private static void RemovePictures(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
{
var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
if (null != shapeContainer)
{
var shapeList = shapeContainer.Children;
for (int i = 0; i < shapeList.Count; i++)
{
var shape = shapeList[i];
if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
{
var picture = (HSSFPicture)shape;
var anchor = (HSSFClientAnchor)shape.Anchor;
if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
{
shapeContainer.RemoveShape(shape);
}
}
}
}
}
//[Obsolete]
//public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderLeft(border, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the left border for a region of cells by manipulating the cell style
/// of the individual cells on the left
/// </summary>
/// <param name="border">The new border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.FirstColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, (int)border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
