private void exportXLSButton_Click(object sender, EventArgs e)
{
if (personListBox.SelectedItem == null)
{
return;
}
saveFileDialog2.ShowDialog();
if (saveFileDialog2.FileName != "")
{
IWorkbook workbook = new XSSFWorkbook();
workbook.CreateSheet("Sheet A1");
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("Дата");
row.CreateCell(1).SetCellValue("Начислено");
row.CreateCell(2).SetCellValue("Оплачено");
int curRow = 1;
row = sheet.CreateRow(curRow);
foreach (Event curEvent in ((Person)personListBox.SelectedItem).events.Where(
x => { return(x.date <= curDateCalendar.SelectionStart); }))
{
if (curEvent.ToRow(row))
{
curRow++;
row = sheet.CreateRow(curRow);
}
}
Person person = (Person)personListBox.SelectedItem;
Report report = person.Simulate(curDateCalendar.SelectionStart);
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
row.CreateCell(0).SetCellValue("Итого на " + curDateCalendar.SelectionStart.ToString().Substring(0, 11));
row.CreateCell(1).SetCellValue("Долг:");
row.CreateCell(2).SetCellValue(report.totalDepth);
row.CreateCell(3).SetCellValue("Пеня:");
row.CreateCell(4).SetCellValue(report.totalPenalty);
row.CreateCell(5).SetCellValue("Долг+пеня:");
row.CreateCell(6).SetCellValue(report.totalDepth + report.totalPenalty);
for (int i = 0; i < 7; i++)
{
sheet.AutoSizeColumn(i);
}
FileStream sw = File.Create(saveFileDialog2.FileName);
workbook.Write(sw);
sw.Close();
}
}
public void CreateSheet(DataTable data, XSSFSheet sheet, ICollection <LookUpColumns> columns)
{
//CreateHeader
var header = sheet.CreateRow(0);
header.CreateCell(0);
int i = 0, j = 0;
columns.Add(new LookUpColumns {
Id = 0, ColumnName = "Id", Unique = true, DataType = "int", Nullable = false
});
foreach (LookUpColumns col in columns.OrderBy(c => c.Id))
{
var cell = header.CreateCell(i);
cell.SetCellValue(col.ColumnName);
sheet.AutoSizeColumn(i);
i++;
}
//CreateRows
i = 0;
foreach (DataRow dr in data.Rows)
{
var row = sheet.CreateRow(i + 1);
j = 0;
foreach (LookUpColumns col in columns.OrderBy(c => c.Id))
{
row.CreateCell(j).SetCellValue(dr[col.ColumnName].ToString());
j++;
}
i++;
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("Лист 1");
int countColumn = 2;
for (int i = 0; i < Liste.Count; i++)
{
var currentRow = sh.CreateRow(i);
for (int j = 0; j < countColumn; j++)
{
var currentCell = currentRow.CreateCell(j);
if (j == 0)
{
currentCell.SetCellValue(Liste[i].Name);
}
if (j == 1)
{
currentCell.SetCellValue(Liste[i].Email);
}
sh.AutoSizeColumn(j);
}
}
if (!File.Exists("d:\\vuzkazahstan.xlsx"))
{
File.Delete("d:\\vuzkazahstan.xlsx");
}
using (var fs = new FileStream("d:\\vuzkazahstan.xlsx", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
Process.Start("d:\\vuzkazahstan.xlsx");
Liste.Clear();
}
/// <summary>
/// Pone un autoFit en las columnas
/// </summary>
private void PutFitInCells()
{
int noOfColumns = _currentsheet.GetRow(_rowInicial - 1).LastCellNum;
for (var j = 0; j < noOfColumns; j++)
{
_currentsheet.AutoSizeColumn(j, false);
}
}
public void WriteExcel(Dictionary <string, LocText> arg)
{
arg = arg.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value);
XSSFWorkbook workbook = new XSSFWorkbook(); //新建xlsx工作簿
workbook.CreateSheet("Sheet1");
XSSFSheet sheet = workbook.GetSheet("Sheet1") as XSSFSheet;
string[] keys = arg.Keys.ToArray();
int header_length = 1;
IRow field = sheet.CreateRow(0);
ICellStyle wrapCellStyle = workbook.CreateCellStyle();
wrapCellStyle.WrapText = true;
wrapCellStyle.Alignment = HorizontalAlignment.Left;
wrapCellStyle.VerticalAlignment = VerticalAlignment.Top;
ICellStyle jpCellStyle = workbook.CreateCellStyle();
jpCellStyle.Alignment = HorizontalAlignment.Left;
jpCellStyle.VerticalAlignment = VerticalAlignment.Top;
jpCellStyle.WrapText = true;
field.CreateCell(0).SetCellValue("Key");
field.CreateCell(1).SetCellValue("日本語");
field.CreateCell(2).SetCellValue("简体中文");
field.CreateCell(3).SetCellValue("繁體中文");
for (int i = 0; i < keys.Length; i++)
{
IRow row = sheet.CreateRow(header_length + i);
ICell cellKey = row.CreateCell(0);
cellKey.SetCellType(CellType.String);
cellKey.SetCellValue(keys[i]);
ICell celljp = row.CreateCell(1);
celljp.SetCellType(CellType.String);
celljp.SetCellValue(arg[keys[i]].ja_JP);
celljp.CellStyle = wrapCellStyle;
ICell cellcn = row.CreateCell(2);
cellcn.SetCellType(CellType.String);
cellcn.SetCellValue(arg[keys[i]].zh_CN);
cellcn.CellStyle = wrapCellStyle;
}
//列宽自适应
for (int i = 0; i <= keys.Length % 1000; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = File.Create(mFilePath))
{
workbook.Write(fs);
workbook.Close();
}
}
private void DgvToXlsx(string fileName, DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2007格式文件（*.xlsx）|*.xlsx";
sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(fileName);
XSSFRow headRow = (XSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
#endregion
MessageBox.Show("恭喜，导出成功");
}
public string ExciseRpt(List <ExciseFreeApply> data)
{
string templateFilePath = Path.Combine(HostingEnvironment.MapPath("~/ExcelTemplate"), "ExciseFreeApplyTemplate.xlsx");
// Open Template
FileStream fs = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);
// Load the template into a NPOI workbook
XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs);
// Load the sheet you are going to use as a template into NPOI
XSSFSheet sheet = (XSSFSheet)templateWorkbook.GetSheetAt(0);
// 建立新頁籤並命名Rpt1
templateWorkbook.CreateSheet("Rpt1");
XSSFSheet sheet2 = (XSSFSheet)templateWorkbook.GetSheetAt(1);
//寬度格式設定
int[] ColWidthSetArr = new int[] { 1, 13, 32, 4, 7,
9, 10, 1, 11, 1 };
SetSheetColumnWidth(ref sheet2, ColWidthSetArr);
//設定將所有欄放入單一頁面中
sheet2.FitToPage = true;
sheet2.PrintSetup.FitWidth = 1;
sheet2.PrintSetup.FitHeight = 0;
//設定列印格式為A4(A3=8,A4=9,Letter=1)
sheet2.PrintSetup.PaperSize = 9;
//基本參數
int originalSheetLastRow = sheet.LastRowNum;
int headerRowLen = 7;
int templateBodyRow = 7;
int footerRowStart = 14;
int footerRowLen = originalSheetLastRow - footerRowStart;
//欄位資訊
int newSheetRowLen = headerRowLen + data.Count + (sheet.LastRowNum - footerRowStart);
int newSheetPageNum = Convert.ToInt16(Math.Ceiling(newSheetRowLen / 44.0));
var tempdata = new ExciseFreeApply();
tempdata.ID = data.Count == 0 ? "" : data[0].ID;
tempdata.Name = data.Count == 0 ? "" : data[0].Name;
tempdata.Address = data.Count == 0 ? "" : data[0].Address;
//表頭
ReplaceRowCellValue(ref sheet, 5, 1, "#0", tempdata.ID);
//表尾
ReplaceRowCellValue(ref sheet, 17, 4, "#1", tempdata.Name);
ReplaceRowCellValue(ref sheet, 18, 4, "#2", tempdata.Address);
for (var i = 0; i < headerRowLen; i++)
{
CopySheetRow(ref sheet, ref sheet2, i, i, false, false, true, false);
}
int sheet2Last = sheet2.LastRowNum + 1;
for (var i = 0; i < data.Count; i++)
{
if (i == 0)
{
SetRowCellValue(ref sheet, templateBodyRow, 1, data[i].ProdTaxNumber);
SetRowCellValue(ref sheet, templateBodyRow, 2, data[i].ProdEngName + data[i].ProdChName);
SetRowCellValue(ref sheet, templateBodyRow, 3, data[i].TaxUnits);
SetRowCellValue(ref sheet, templateBodyRow, 4, (double)data[i].Qty);
SetRowCellValue(ref sheet, templateBodyRow, 5, ToSimpleTaiwanDate(data[i].ProcessDate));
SetRowCellValue(ref sheet, templateBodyRow, 6, data[i].SheetNumber);
SetRowCellValue(ref sheet, templateBodyRow, 7, data[i].Mode);
CopySheetRow(ref sheet, ref sheet2, templateBodyRow, sheet2Last + i, false, false, true, true);
}
else
{
SetRowCellValue(ref sheet, templateBodyRow + 1, 1, data[i].ProdTaxNumber);
SetRowCellValue(ref sheet, templateBodyRow + 1, 2, data[i].ProdEngName + data[i].ProdChName);
SetRowCellValue(ref sheet, templateBodyRow + 1, 3, data[i].TaxUnits);
SetRowCellValue(ref sheet, templateBodyRow + 1, 4, (double)data[i].Qty);
SetRowCellValue(ref sheet, templateBodyRow + 1, 5, ToSimpleTaiwanDate(data[i].ProcessDate));
SetRowCellValue(ref sheet, templateBodyRow + 1, 6, data[i].SheetNumber);
SetRowCellValue(ref sheet, templateBodyRow + 1, 7, data[i].Mode);
CopySheetRow(ref sheet, ref sheet2, templateBodyRow + 1, sheet2Last + i, false, false, false, false);
}
}
sheet2.CreateRow(sheet2.LastRowNum + 1);
sheet2.CopyRow(sheet2.LastRowNum - 1, sheet2.LastRowNum + 1);
sheet2Last = sheet2.LastRowNum + 1;
for (var i = 0; i < footerRowLen; i++)
{
CopySheetRow(ref sheet, ref sheet2, footerRowStart + i, sheet2Last + i, false, false, true, false);
}
//自動調整欄位寬度，此欄位為數量
sheet2.AutoSizeColumn(4);
//合併儲存格
if (data.Count > 0)
{
RemoveMergeCells(ref sheet2, headerRowLen);
sheet2.AddMergedRegion(new CellRangeAddress(headerRowLen, sheet2.LastRowNum - footerRowLen, 8, 8));
}
string filePath = @"C:\temp\excel";
//建folder
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
//新完整檔名
string newFileName = "export.xlsx";
//檔案路徑 + 新完整檔名
string fullFilePath = Path.Combine(filePath, newFileName);
FileStream file = new FileStream(fullFilePath, FileMode.Create);//產生檔案
templateWorkbook.Write(file);
file.Close();
GC.Collect();
return(fullFilePath);
}
public string ExportacionCAP(string HtmlDoc, bool Soar)
{
var random = new Random();
string token = new string(Enumerable.Repeat("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 30).Select(s => s[random.Next(s.Length)]).ToArray());
XSSFWorkbook xwb = new XSSFWorkbook();
XSSFSheet xs = (XSSFSheet)xwb.CreateSheet("CAP");
XSSFRow xcr = (XSSFRow)xs.CreateRow(0);
XSSFCell xcc;
XSSFCellStyle cstl;
XSSFFont cfnt;
XSSFColor ccol;
int iCell = 0;
int iRow = 1;
HtmlDocument ht = new HtmlDocument();
ht.LoadHtml(HtmlDoc);
HtmlNodeCollection Tables = ht.DocumentNode.SelectNodes("//body/table");
HtmlNodeCollection HeaderRows;
HtmlNodeCollection Rows;
HtmlNodeCollection ValueRows;
for (int t = 0; t < Tables.Count; t++)
{
if ((Soar && t == 0) || !Soar)
{
iCell = 0;
if (Tables.IndexOf(Tables[t]) != 0)
{
iRow += Soar?1:2;
}
xcr = (XSSFRow)xs.CreateRow(iRow - 1);
xcc = (XSSFCell)xcr.CreateCell(iCell);
HeaderRows = Tables[t].SelectNodes("./thead/tr/th");
foreach (HtmlNode TableHeader in HeaderRows)
{
xcc = (XSSFCell)xcr.CreateCell(iCell);
string[] arr = TableHeader.Attributes["style"].Value.ToString().Split(';');
int[] BackRgb = new int[3];
string[] BackRgbStr;
int[] FrontRgb = new int[3];
string[] FrontRgbStr;
foreach (string style in arr)
{
if (style.Trim().StartsWith("background-color:"))
{
BackRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < BackRgbStr.Length; i++)
{
BackRgb[i] = int.Parse(BackRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
if (style.Trim().StartsWith("color:"))
{
FrontRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < FrontRgbStr.Length; i++)
{
FrontRgb[i] = int.Parse(FrontRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
for (int i = 0; i < 3; i++)
{
if (BackRgb[i] == 0)
{
BackRgb[i] = 255;
}
}
cstl = (XSSFCellStyle)xwb.CreateCellStyle();
cfnt = (XSSFFont)xwb.CreateFont();
ccol = (!Soar? new XSSFColor(Color.FromArgb(BackRgb[0], BackRgb[1], BackRgb[2])): new XSSFColor((iCell != 0 && iCell != 1?Color.FromArgb(253, 234, 218): Color.FromArgb(255, 255, 255))));
cstl.SetFillForegroundColor(ccol);
cstl.FillPattern = FillPattern.SolidForeground;
ccol = (!Soar? new XSSFColor(Color.FromArgb(FrontRgb[0], FrontRgb[1], FrontRgb[2])): new XSSFColor(Color.FromArgb(0, 0, 255)));
cfnt.SetColor(ccol);
cfnt.IsBold = true;
cfnt.FontHeightInPoints = (short)(!Soar?14:10);
cstl.SetFont(cfnt);
if (Soar)
{
cstl.BorderBottom = BorderStyle.Thin;
}
if (!Soar)
{
cstl.Alignment = HorizontalAlignment.Center;
}
xcc.CellStyle = cstl;
xs.AutoSizeColumn(xcc.ColumnIndex);
}
xcc.SetCellValue(Soar ? TableHeader.InnerHtml.Split('(')[0] : TableHeader.InnerHtml);
iCell++;
}
}
Rows = Tables[t].SelectNodes("./tbody/tr");
iCell = 0;
foreach (HtmlNode Row in Rows)
{
xcr = (XSSFRow)xs.CreateRow(iRow++);
ValueRows = Row.SelectNodes("./td");
iCell = 0;
foreach (HtmlNode RowData in ValueRows)
{
string[] arr = RowData.Attributes["style"].Value.ToString().Split(';');
int[] BackRgb = new int[3];
string[] BackRgbStr;
int[] FrontRgb = new int[3];
string[] FrontRgbStr;
bool bRightBorder = false;
xcc = (XSSFCell)xcr.CreateCell(iCell++);
foreach (string style in arr)
{
if (style.Trim().StartsWith("background-color:"))
{
BackRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < BackRgbStr.Length; i++)
{
BackRgb[i] = int.Parse(BackRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
if (style.Trim().StartsWith("color:"))
{
FrontRgbStr = style.Split(':')[1].Split(',');
for (int i = 0; i < FrontRgbStr.Length; i++)
{
FrontRgb[i] = int.Parse(FrontRgbStr[i].Replace("rgb(", "").Replace(")", ""));
}
}
if (style.Trim().StartsWith("border-right: 1px dashed black"))
{
bRightBorder = true;
}
for (int i = 0; i < 3; i++)
{
if (BackRgb[i] == 0)
{
BackRgb[i] = 255;
}
}
cstl = (XSSFCellStyle)xwb.CreateCellStyle();
cfnt = (XSSFFont)xwb.CreateFont();
ccol = new XSSFColor(Color.FromArgb(BackRgb[0], BackRgb[1], BackRgb[2]));
cstl.SetFillForegroundColor(ccol);
cstl.FillPattern = FillPattern.SolidForeground;
if (bRightBorder)
{
cstl.BorderRight = BorderStyle.DashDot;
}
ccol = new XSSFColor(Color.FromArgb(FrontRgb[0], FrontRgb[1], FrontRgb[2]));
cfnt.SetColor(ccol);
cfnt.FontHeightInPoints = (short)(!Soar ? 12 : 11);
cstl.SetFont(cfnt);
if (!Soar)
{
cstl.Alignment = HorizontalAlignment.Center;
}
xcc.CellStyle = cstl;
xs.AutoSizeColumn(xcc.ColumnIndex);
}
int val = int.MinValue;
bool bVal = int.TryParse(RowData.InnerHtml, out val);
if (bVal)
{
xcc.SetCellType(CellType.Numeric);
xcc.SetCellValue(Convert.ToInt32(RowData.InnerHtml));
}
else
{
xcc.SetCellValue(RowData.InnerHtml.ToUpper());
}
}
}
}
FileStream File = new FileStream(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Temp", token + ".xlsx"), FileMode.Create, FileAccess.Write);
xwb.Write(File);
File.Close();
return(token);
}
public void computeResult(string uniqueID, IDictionary <int, string> eventDict, XSSFWorkbook wb, string sheetName, List <string> responseList,
List <string> questionTypeList, IDictionary <string, string> uniqueAnswerDict,
List <string> questionList, List <List <string> > answerList, string resultType = "Normal")
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 0;
int totalNumChoiceLength = 0;
if (resultType != "Percent")
{
XSSFCreationHelper createHelper = (XSSFCreationHelper)wb.GetCreationHelper();
}
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
//Group all the different answers list together
foreach (KeyValuePair <string, string> entry in uniqueAnswerDict)
{
// Create a row and put some cells in it. Rows are 0 based.
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(entry.Value));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
int cellCount = 1;
List <string> answerChoiceList = JsonConvert.DeserializeObject <List <string> >(entry.Key);
if (answerChoiceList.Count > totalNumChoiceLength)
{
totalNumChoiceLength = answerChoiceList.Count;
}
foreach (string choice in answerChoiceList)
{
row.CreateCell(cellCount).SetCellValue(choice);
row.GetCell(cellCount).CellStyle = xStyle;
cellCount += 1;
}
rowCount += 1;
}
xStyle = boldFont(wb);
//xStyle.FillPattern = FillPattern.NoFill;
List <string> openEndedList = new List <string>();
Dictionary <int, List <string> > openEndedResponseDict = new Dictionary <int, List <string> >();
Dictionary <string, int> choiceResponseDict = new Dictionary <string, int>();
//Get a list of response
foreach (var response in responseList)
{
FeedbackEntity feedbackEntity = JsonConvert.DeserializeObject <FeedbackEntity>(response);
List <string> feedbackResponse = JsonConvert.DeserializeObject <List <string> >(feedbackEntity.Response);
int qnNo = 1;
foreach (string feedback in feedbackResponse)
{
List <string> openEndedResponse = new List <string>();
if (questionTypeList[qnNo - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
if (openEndedResponseDict.ContainsKey(qnNo))
{
openEndedResponseDict[qnNo].Add(feedback);
}
else
{
openEndedResponse.Add(feedback);
openEndedResponseDict.Add(qnNo, openEndedResponse);
}
}
else if (questionTypeList[qnNo - 1] == "2") //Multiple Choice
{
if (choiceResponseDict.ContainsKey(qnNo + "||" + feedback))
{
choiceResponseDict[qnNo + "||" + feedback] += 1;
}
else
{
choiceResponseDict.Add(qnNo + "||" + feedback, 1);
}
}
qnNo += 1;
}
}
//Print out all the question number and text
int qnCount = 1;
foreach (var qnText in questionList)
{
if (questionTypeList[qnCount - 1] == "1") //OpenEnded Question
{
//Store it in a string so that you can write it in later
openEndedList.Add("Q" + qnCount + "." + qnText);
}
else if (questionTypeList[qnCount - 1] == "2") //Multiple Choice
{
xStyle = boldFont(wb);
XSSFColor color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue("Q" + qnCount + ". ");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(0 + totalNumChoiceLength + 1).SetCellValue(qnText);
xStyle = normalFont(wb);
color = new XSSFColor();
color.SetRgb(JsonConvert.DeserializeObject <byte[]>(uniqueAnswerDict[JsonConvert.SerializeObject(answerList[qnCount - 1])]));
xStyle.SetFillForegroundColor(color);
xStyle.FillPattern = FillPattern.SolidForeground;
for (int i = 0; i < answerList[qnCount - 1].Count; i++)
{
//Print out all the question response
if (choiceResponseDict.ContainsKey(qnCount + "||" + answerList[qnCount - 1][i]))
{
double displayValue = 0;
if (resultType == "Percent")
{
double value = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
displayValue = value / responseList.Count * 100;
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2) + "%");
}
else
{
displayValue = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]];
row.CreateCell(1 + i).SetCellValue(Math.Round(displayValue, 2));
}
row.GetCell(1 + i).CellStyle = xStyle;
}
else
{
row.CreateCell(1 + i).SetCellValue(0);
row.GetCell(1 + i).CellStyle = xStyle;
}
xStyle.SetFillForegroundColor(color); //to end off the color xStyle
}
rowCount += 1;
}
qnCount++;
}
summarySheet.CreateRow(rowCount);
rowCount++;
xStyle = boldFont(wb);
//Print out all the openEnded Questions
foreach (var openEnded in openEndedList)
{
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue(openEnded);
row.GetCell(0).CellStyle = xStyle;
rowCount += 1;
int qnNo = Convert.ToInt32(openEnded.Split('.')[0].Remove(0, 1));
//Create rows for response answers
if (openEndedResponseDict.ContainsKey(qnNo))
{
foreach (var response in openEndedResponseDict[qnNo])
{
xStyle = normalFont(wb);
row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(1).SetCellValue(response);
row.GetCell(1).CellStyle = xStyle;
rowCount += 1;
}
}
else
{
rowCount += 1;
}
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}
private void PrintReport()
{
var firstRowNumber = 0;
var firstDataRowNumber = firstRowNumber + _headerLength;
var headerRow = _sheet.CreateRow(_headerLength) as XSSFRow;
var column = 0;
foreach (var item in _configurationList)
{
if (!item.IsVisible)
{
continue;
}
var titleCell = headerRow.CreateCell(column) as XSSFCell;
titleCell.SetCellType(CellType.String);
titleCell.SetCellValue(item.FriendlyName);
titleCell.CellStyle = _headerStyle;
for (var index = 0; index < _model.Count; index++)
{
XSSFRow dataRow;
if (column == 0)
{
dataRow = _sheet.CreateRow(_headerLength + index + 1) as XSSFRow;
}
else
{
dataRow = _sheet.GetRow(_headerLength + index + 1) as XSSFRow;
}
var dataCell = dataRow.CreateCell(column) as XSSFCell;
dataCell.SetCellType(CellType.String);
if (item.Name.IndexOf("Date", StringComparison.InvariantCulture) > -1)
{
dataCell.CellStyle = _dateGridStyle;
}
else
{
dataCell.CellStyle = _generalGridStyle;
}
var value = GetValue(index, item);
if (value != null)
{
dataCell.SetCellValue(value);
}
}
_sheet.AutoSizeColumn(column);
if (_sheet.GetColumnWidth(column) < 3000)
{
_sheet.SetColumnWidth(column, 3000);
}
if (_sheet.GetColumnWidth(column) > 13000)
{
_sheet.SetColumnWidth(column, 13000);
}
if (_sheet.GetColumnWidth(column) < 13000 - 512)
{
_sheet.SetColumnWidth(column, _sheet.GetColumnWidth(column) + 512);
}
column++;
}
BuildCriteriaHeader();
var filterRange = new CellRangeAddress(firstDataRowNumber, _sheet.LastRowNum, headerRow.FirstCellNum,
headerRow.LastCellNum - 1);
_sheet.SetAutoFilter(filterRange);
_sheet.CreateFreezePane(0, firstDataRowNumber + 1);
_sheet.DisplayGridlines = false;
}
/// <summary>
/// 导出到Excel函数
/// </summary>
private bool ExportToExcel(string fileName, string[] columnName, string date)
{
bool bRet = true;
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Excel 文件(*.xlsx;*.xls)|*.xlsx;*.xls";
sfd.FileName = fileName;
if (sfd.ShowDialog() != DialogResult.OK)
{
bRet = false;
return(bRet);
}
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(fileName);
sheet.PrintSetup.PaperSize = 9; // A4打印形式
sheet.PrintSetup.Landscape = false; // 纵向打印
var p = fileName.Split('_');
#region 创建标题名称
XSSFRow title = (XSSFRow)sheet.CreateRow(0);
for (int j = 0; j < columnName.Count(); j++)
{
XSSFCell headCell = (XSSFCell)title.CreateCell(j, CellType.String);
}
// 合并单元格
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, (columnName.Count() - 1));
sheet.AddMergedRegion(region1);
title.CreateCell(0).SetCellValue("庄信万丰（上海）化工有限公司装载机数据表");
ICellStyle titleStyle = workbook.CreateCellStyle();// 样式
// 设置单元格的样式：水平对齐居中
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// 将新的样式赋给单元格
title.GetCell(0).CellStyle = titleStyle;
#endregion
#region 创建日期
XSSFRow dateRow = (XSSFRow)sheet.CreateRow(1);
for (int j = 0; j < columnName.Count(); j++)
{
XSSFCell headCell = (XSSFCell)dateRow.CreateCell(j, CellType.String);
}
// 合并单元格
CellRangeAddress region3 = new CellRangeAddress(1, 1, 0, (columnName.Count() - 1));
sheet.AddMergedRegion(region3);
string tempDate = "导出时间:" + date;
dateRow.CreateCell(0).SetCellValue(tempDate);
#endregion
// 加载数据
XSSFRow headRow = (XSSFRow)sheet.CreateRow(2);
for (int i = 0; i < columnName.Count(); i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(columnName[i]);
headCell.CellStyle = AddStytle(workbook);
}
for (int i = 0; i < dictData.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 3);
for (int j = 0; j < dictData[i].Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
cell.CellStyle = AddStytle(workbook);
if (dictData[i][j] == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dictData[i][j] is string)
{
cell.SetCellValue(dictData[i][j].ToString());
}
else if (dictData[i][j] is decimal)
{
cell.SetCellValue(Convert.ToSingle(dictData[i][j]));
}
else if (dictData[i][j] is int)
{
cell.SetCellValue(Convert.ToInt32(dictData[i][j]));
}
else if (dictData[i][j] is double)
{
cell.SetCellValue(Convert.ToDouble(dictData[i][j]));
}
else if (dictData[i][j] is Decimal)
{
cell.SetCellValue(Convert.ToDouble(dictData[i][j]));
}
else if (dictData[i][j] is DateTime)
{
cell.SetCellValue(Convert.ToDateTime(dictData[i][j]).ToString("yyyy-MM-dd hh:ss:mm"));
}
}
}
}
for (int i = 0; i < columnName.Count(); i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
workbook.Write(fs);
}
#endregion
AutoDeleteMessageBox Auto = new AutoDeleteMessageBox(); // 自动关闭窗口
MessageBox.Show("恭喜，导出成功!", "MessageBox");
return(bRet);
}
private void PrintReport()
{
BuildCriteriaHeader();
var rowNumber = 0;
foreach (var item in (_detail as IHaveConfigurationList).ConfigurationList)
{
if (!item.IsVisible)
{
continue;
}
var row = _sheet.CreateRow(_headerLength + rowNumber) as XSSFRow;
var titleCell = row.CreateCell(0) as XSSFCell;
titleCell.SetCellType(CellType.String);
titleCell.SetCellValue(item.FriendlyName);
titleCell.CellStyle = _generalBoldStyle;
var dataCell = row.CreateCell(1) as XSSFCell;
dataCell.SetCellType(CellType.String);
if (item.Name.IndexOf("Date", StringComparison.InvariantCulture) > -1)
{
dataCell.CellStyle = _dateGridStyle;
}
else
{
dataCell.CellStyle = _generalGridStyle;
}
var value = GetValue(item);
if (value != null)
{
dataCell.SetCellValue(value);
}
rowNumber++;
var thinRow = _sheet.CreateRow(_headerLength + rowNumber) as XSSFRow;
thinRow.Height = 100;
rowNumber++;
}
for (var column = 0; column < 2; column++)
{
_sheet.AutoSizeColumn(column);
if (_sheet.GetColumnWidth(column) < 3000)
{
_sheet.SetColumnWidth(column, 3000);
}
if (_sheet.GetColumnWidth(column) > 26000)
{
_sheet.SetColumnWidth(column, 26000);
}
if (_sheet.GetColumnWidth(column) < 26000 - 512)
{
_sheet.SetColumnWidth(column, _sheet.GetColumnWidth(column) + 512);
}
}
_sheet.DisplayGridlines = false;
}
public void attendanceResult(string surveyCode, XSSFWorkbook wb, string sheetName)
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 4;
//XSSFCreationHelper createHelper = (XSSFCreationHelper)wb.GetCreationHelper();
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
// Create a row and put some cells in it. Rows are 0 based.
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
//Create Titles for the raw results
row.CreateCell(0).SetCellValue("No.");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(1).SetCellValue("EventCode");
row.GetCell(1).CellStyle = xStyle;
row.CreateCell(2).SetCellValue("Date");
row.GetCell(2).CellStyle = xStyle;
row.CreateCell(3).SetCellValue("Name");
row.GetCell(3).CellStyle = xStyle;
row.CreateCell(4).SetCellValue("Morning");
row.GetCell(4).CellStyle = xStyle;
row.CreateCell(5).SetCellValue("Afternoon");
row.GetCell(5).CellStyle = xStyle;
row.CreateCell(6).SetCellValue("Survey");
row.GetCell(6).CellStyle = xStyle;
rowCount += 1;
//Get results from attendance
var storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings["AzureWebJobsStorage"]);
var tableClient = storageAccount.CreateCloudTableClient();
CloudTable attendanceTable = tableClient.GetTableReference("Attendance");
TableQuery <AttendanceEntity> query = new TableQuery <AttendanceEntity>().Where(TableQuery.GenerateFilterCondition("SurveyCode", QueryComparisons.Equal, surveyCode));
Dictionary <string, int> attendanceDict = new Dictionary <string, int>();
attendanceDict.Add("Morning", 0);
attendanceDict.Add("Afternoon", 0);
attendanceDict.Add("Survey", 0);
int no = 1;
foreach (AttendanceEntity entity in attendanceTable.ExecuteQuery(query))
{
row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue(no);
row.CreateCell(1).SetCellValue(entity.PartitionKey);
row.CreateCell(2).SetCellValue(entity.RowKey);
row.CreateCell(3).SetCellValue(entity.Name);
var morning = "No";
var afternoon = "No";
var survey = "No";
if (entity.Morning)
{
attendanceDict["Morning"] += 1;
morning = "Yes";
}
if (entity.Afternoon)
{
attendanceDict["Afternoon"] += 1;
afternoon = "Yes";
}
if (entity.Survey)
{
attendanceDict["Survey"] += 1;
survey = "Yes";
}
row.CreateCell(4).SetCellValue(morning);
row.CreateCell(5).SetCellValue(afternoon);
row.CreateCell(6).SetCellValue(survey);
rowCount += 1;
no++;
}
//Print out the attendance
List <string> attendanceType = new List <string>()
{
"Morning", "Afternoon", "Survey"
};
XSSFRow newRow;
XSSFCell cell;
for (int i = 0; i < attendanceType.Count; i++)
{
newRow = (XSSFRow)summarySheet.GetRow(i);
if (newRow == null)
{
newRow = (XSSFRow)summarySheet.CreateRow(i);
}
cell = (XSSFCell)newRow.GetCell(0);
if (cell == null)
{
newRow.CreateCell(0).SetCellValue(attendanceType[i]);
}
newRow.GetCell(0).CellStyle = xStyle;
newRow = (XSSFRow)summarySheet.GetRow(i);
if (newRow == null)
{
newRow = (XSSFRow)summarySheet.CreateRow(i);
}
cell = (XSSFCell)newRow.GetCell(1);
if (cell == null)
{
newRow.CreateCell(1).SetCellValue(attendanceDict[attendanceType[i]]);
}
newRow.GetCell(1).CellStyle = xStyle;
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}
public void computeRawResult(XSSFWorkbook wb, string sheetName, List <string> questionList, List <string> responseList)
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 0;
int fixColCount = 4;
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
// Create a row and put some cells in it. Rows are 0 based.
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
//Create Titles for the raw results
row.CreateCell(0).SetCellValue("No.");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(1).SetCellValue("surveyCode");
row.GetCell(1).CellStyle = xStyle;
row.CreateCell(2).SetCellValue("Date");
row.GetCell(2).CellStyle = xStyle;
row.CreateCell(3).SetCellValue("Name");
row.GetCell(3).CellStyle = xStyle;
//Get the total number of questions
int colCount = fixColCount;
for (int i = 0; i < questionList.Count; i++)
{
row.CreateCell(colCount + i).SetCellValue("Q" + (i + 1));
row.GetCell(colCount + i).CellStyle = xStyle;
}
//row.RowStyle.SetFont(font);
rowCount += 1;
//Get all the response and print out to the columns
int responseNo = 1;
foreach (var response in responseList)
{
row = (XSSFRow)summarySheet.CreateRow(rowCount);
FeedbackEntity feedbackEntity = JsonConvert.DeserializeObject <FeedbackEntity>(response);
row.CreateCell(0).SetCellValue(responseNo);
row.CreateCell(1).SetCellValue(feedbackEntity.PartitionKey);
row.CreateCell(2).SetCellValue(feedbackEntity.Date);
row.CreateCell(3).SetCellValue(feedbackEntity.Name);
//row.RowStyle.SetFont(font);
List <string> responseAnswerList = JsonConvert.DeserializeObject <List <string> >(feedbackEntity.Response);
colCount = fixColCount;
foreach (var feedback in responseAnswerList)
{
row.CreateCell(colCount).SetCellValue(feedback);
colCount += 1;
}
responseNo += 1;
rowCount += 1;
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}
public void computeScore(string uniqueID, IDictionary <int, string> eventDict, XSSFWorkbook wb, string sheetName,
List <string> responseList, List <string> questionTypeList, List <string> questionList, List <List <string> > answerList)
{
//Create bold font for excel
XSSFCellStyle xStyle = boldFont(wb);
int rowCount = 0;
XSSFSheet summarySheet = (XSSFSheet)wb.CreateSheet(sheetName);
Dictionary <int, List <string> > openEndedResponseDict = new Dictionary <int, List <string> >();
Dictionary <string, int> choiceResponseDict = new Dictionary <string, int>();
XSSFRow topRow = (XSSFRow)summarySheet.CreateRow(rowCount);
topRow.CreateCell(1).SetCellValue("Asc");
topRow.GetCell(1).CellStyle = xStyle;
topRow.CreateCell(2).SetCellValue("Asc");
topRow.GetCell(2).CellStyle = xStyle;
topRow.CreateCell(3).SetCellValue("Desc");
topRow.GetCell(3).CellStyle = xStyle;
topRow.CreateCell(4).SetCellValue("Desc");
topRow.GetCell(4).CellStyle = xStyle;
rowCount++;
//Get a list of response
foreach (var response in responseList)
{
FeedbackEntity feedbackEntity = JsonConvert.DeserializeObject <FeedbackEntity>(response);
List <string> feedbackResponse = JsonConvert.DeserializeObject <List <string> >(feedbackEntity.Response);
int qnNo = 1;
foreach (string feedback in feedbackResponse)
{
if (questionTypeList[qnNo - 1] == "2") //Multiple Choice
{
if (choiceResponseDict.ContainsKey(qnNo + "||" + feedback))
{
choiceResponseDict[qnNo + "||" + feedback] += 1;
}
else
{
choiceResponseDict.Add(qnNo + "||" + feedback, 1);
}
}
qnNo += 1;
}
}
//Print out all the question number and text
int qnCount = 1;
foreach (var qnText in questionList)
{
if (questionTypeList[qnCount - 1] == "2") //Multiple Choice
{
int naCount = 0;
XSSFRow row = (XSSFRow)summarySheet.CreateRow(rowCount);
row.CreateCell(0).SetCellValue("Q" + qnCount + ". ");
row.GetCell(0).CellStyle = xStyle;
row.CreateCell(5).SetCellValue(qnText);
var ascValue = 0.00;
var descValue = 0.00;
var answerListcount = answerList[qnCount - 1].Count;
if (answerList[qnCount - 1][answerListcount - 1] == "NA" || answerList[qnCount - 1][answerListcount - 1] == "Not Applicable" || answerList[qnCount - 1][answerListcount - 1] == "N.A.")
{
if (choiceResponseDict.ContainsKey(qnCount + "||" + answerList[qnCount - 1][answerListcount - 1]))
{
naCount = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][answerListcount - 1]];
}
answerListcount -= 1;
}
for (int i = 0; i < answerListcount; i++)
{
//Print out all the question response
if (choiceResponseDict.ContainsKey(qnCount + "||" + answerList[qnCount - 1][i]))
{
double displayValue = 0;
double value = choiceResponseDict[qnCount + "||" + answerList[qnCount - 1][i]]; //No. of ppl who choose option
displayValue = value / (responseList.Count - naCount); //Total Response;
ascValue += (displayValue * (i + 1));
descValue += (displayValue * (answerListcount - i));
}
}
row.CreateCell(1).SetCellValue(Math.Round(ascValue, 2));
row.CreateCell(2).SetCellValue(Math.Round(ascValue / answerListcount * 100, 2) + "%");
row.CreateCell(3).SetCellValue(Math.Round(descValue, 2));
row.CreateCell(4).SetCellValue(Math.Round(descValue / answerListcount * 100, 2) + "%");
rowCount += 1;
}
qnCount++;
}
for (int i = 1; i <= 15; i++) // this will aply it form col 1 to 10
{
summarySheet.AutoSizeColumn(i);
}
}
/// <summary>
/// export the data
/// </summary>
/// <param name="lst"></param>
/// <param name="fileName"></param>
/// <param name="lFolder"></param>
/// <param name="rFolder"></param>
/// <returns></returns>
public static bool Export(List <Level> lst, string fileName, string lFolder, string rFolder)
{
bool isSuccess = false;
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workBook.CreateSheet();
try
{
//add header style
ICellStyle cellStyle = workBook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workBook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 12;
font.FontName = "Arial Unicode MS";
cellStyle.SetFont(font);
#region header settings
IRow first = sheet.CreateRow(0);
ICell fst_cell = first.CreateCell(0);
fst_cell.SetCellValue("Quarterly Source Code Comparison and Retrofit ");
fst_cell.CellStyle = cellStyle;
IRow second = sheet.CreateRow(1);
IRow third = sheet.CreateRow(2);
third.CreateCell(0).SetCellValue("Date:");
string[,] arr = new string[2, 4] {
{ "GIT Version:", "", "Source Path(L):", lFolder }, { "Production Version:", "", "Source Path(R):", rFolder }
};
for (int i = 0; i < 2; i++)
{
IRow forth_fifth = sheet.CreateRow(i + 3);
for (int j = 0; j < 4; j++)
{
forth_fifth.CreateCell(j).SetCellValue(arr[i, j]);
}
}
IRow sixth = sheet.CreateRow(5);
IRow header = sheet.CreateRow(6);
header.HeightInPoints = 12;
List <string> headers = new List <string> {
"Production Version", "GIT Version", ""
};
ICellStyle cs = workBook.CreateCellStyle();
cs.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cs.FillPattern = FillPattern.SolidForeground;
cs.Alignment = HorizontalAlignment.Center;
cs.VerticalAlignment = VerticalAlignment.Center;
cs.SetFont(font);
cs.BorderTop = BorderStyle.Thin;
cs.BorderRight = BorderStyle.Thin;
cs.BorderBottom = BorderStyle.Thin;
cs.BorderLeft = BorderStyle.Thin;
for (int m = 0; m < headers.Count; m++)
{
CellRangeAddress region = new CellRangeAddress(6, 6, m * 3, m * 3 + 2);
sheet.AddMergedRegion(region);
for (int n = 0; n < 3; n++)
{
ICell cell = header.CreateCell(3 * m + n);
cell.SetCellValue(headers[m]);
cell.CellStyle = cs;
}
}
IRow title = sheet.CreateRow(7);
title.HeightInPoints = 12;
ICellStyle titleStyle = workBook.CreateCellStyle();
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.Alignment = HorizontalAlignment.Left;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.SetFont(font);
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
List <string> titles = new List <string> {
"Path", "Filename", "Size(Byte)", "Path", "FileName", "Size(Byte)", "Comparision Result", "Check-in GIT", "Need Check", "Remark"
};
for (int j = 0; j < titles.Count; j++)
{
ICell cell = title.CreateCell(j);
cell.SetCellValue(titles[j]);
cell.CellStyle = titleStyle;
}
#endregion
sheet.SetAutoFilter(new CellRangeAddress(8, 8, 0, titles.Count - 2)); //line filter
sheet.CreateFreezePane(0, 8); //line freeze
#region body
for (int i = 0; i < lst.Count; i++)
{
sheet.AutoSizeColumn(i);
IRow body = sheet.CreateRow(i + 8);
string lpath = string.Empty;
string lfile = "NA";
string lsize = "NA";
string rpath = string.Empty;
string rfile = "NA";
string rsize = "NA";
string result = "NA";
if (lst[i].LeftNode != null)
{
lpath = lst[i].LeftNode.Path;
lfile = lst[i].LeftNode.FileName;
result = lst[i].LeftNode.Result;
lsize = lst[i].LeftNode.Size;
}
else
{
lpath = lst[i].LtPath;
}
if (lst[i].RightNode != null)
{
rpath = lst[i].RightNode.Path;
rfile = lst[i].RightNode.FileName;
result = lst[i].RightNode.Result;
rsize = lst[i].RightNode.Size;
}
else
{
rpath = lst[i].RtPath;
}
switch (result)
{
case "ltonly":
result = $"Only exists in {lst[i].LeftNode.Path}";
break;
case "rtonly":
result = $"Only exists in {lst[i].RightNode.Path}";
break;
case "same":
if (lsize.Equals(rsize))
{
result = "same";
}
else
{
result = "same text but different size";
}
break;
default:
result = "Text files are different";
break;
}
body.CreateCell(0).SetCellValue(lpath);
body.CreateCell(1).SetCellValue(lfile);
body.CreateCell(2).SetCellValue(lsize);
body.CreateCell(3).SetCellValue(rpath);
body.CreateCell(4).SetCellValue(rfile);
body.CreateCell(5).SetCellValue(rsize);
body.CreateCell(6).SetCellValue(result);
}
SetColumnWidth(sheet, titles.Count);
#endregion
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workBook.Write(fs);
workBook.Close();
}
isSuccess = true;
}
catch (Exception ex)
{
ex.Message.Logger();
workBook.Close();
}
return(isSuccess);
}
public byte[] ExprotExcel(IList <客戶資料> data)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Export");
XSSFCellStyle styletital = (XSSFCellStyle)workbook.CreateCellStyle();
styletital.Alignment = HorizontalAlignment.Center;
styletital.BorderBottom = BorderStyle.Thin;
styletital.BorderLeft = BorderStyle.Thin;
styletital.BorderRight = BorderStyle.Thin;
styletital.BorderTop = BorderStyle.Thin;
styletital.FillForegroundColor = HSSFColor.Grey25Percent.Index;
styletital.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle stylecon = (XSSFCellStyle)workbook.CreateCellStyle();
stylecon.Alignment = HorizontalAlignment.Left; //對齊
stylecon.BorderBottom = BorderStyle.Thin;
stylecon.BorderLeft = BorderStyle.Thin;
stylecon.BorderRight = BorderStyle.Thin;
stylecon.BorderTop = BorderStyle.Thin;
#region HeaderRow
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("客戶名稱");
headerRow.Cells[0].CellStyle = styletital;
headerRow.CreateCell(1).SetCellValue("統一編號");
headerRow.Cells[1].CellStyle = styletital;
headerRow.CreateCell(2).SetCellValue("電話");
headerRow.Cells[2].CellStyle = styletital;
headerRow.CreateCell(3).SetCellValue("傳真");
headerRow.Cells[3].CellStyle = styletital;
headerRow.CreateCell(4).SetCellValue("地址");
headerRow.Cells[4].CellStyle = styletital;
headerRow.CreateCell(5).SetCellValue("Email");
headerRow.Cells[5].CellStyle = styletital;
headerRow.CreateCell(6).SetCellValue("客戶分類");
headerRow.Cells[6].CellStyle = styletital;
#endregion
#region DetailRow
int rowIndex = 1;
foreach (var item in data)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(item.客戶名稱);
dataRow.Cells[0].CellStyle = stylecon;
dataRow.CreateCell(1).SetCellValue(item.統一編號);
dataRow.Cells[1].CellStyle = stylecon;
dataRow.CreateCell(2).SetCellValue(item.電話);
dataRow.Cells[2].CellStyle = stylecon;
dataRow.CreateCell(3).SetCellValue(item.傳真);
dataRow.Cells[3].CellStyle = stylecon;
dataRow.CreateCell(4).SetCellValue(item.地址);
dataRow.Cells[4].CellStyle = stylecon;
dataRow.CreateCell(5).SetCellValue(item.Email);
dataRow.Cells[5].CellStyle = stylecon;
dataRow.CreateCell(6).SetCellValue(item.客戶分類);
dataRow.Cells[6].CellStyle = stylecon;
rowIndex++;
}
#endregion
for (int i = 0; i < sheet.GetRow(0).Cells.Count; i++)
{
sheet.AutoSizeColumn(i);
}
// code to create workbook
byte[] fileContents = null;
using (var memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
fileContents = memoryStream.ToArray();
}
return(fileContents);
}
public static void ListToSheetXlsx <T>(XSSFWorkbook workbook, List <T> list, string sheetName)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(sheetName);
XSSFCellStyle headStyle = workbook.GetHeadStyle();
//值类型直接返回第一列
Type tp = typeof(T);
//属性列表
PropertyInfo[] properties = tp.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
//property.Name是属性的英文名，怎么转换成中文？使用DescriptionAttribute特性
List <string> fieldStringArray = new List <string>();
List <PropertyInfo> propertiesUsed = new List <PropertyInfo>();
foreach (var property in properties)
{
if (Attribute.IsDefined(property, typeof(DescriptionAttribute)))
{
fieldStringArray.Add(property.GetEnumDescription());
propertiesUsed.Add(property);
}
}
int fieldCount = fieldStringArray.Count;
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
for (int i = 0; i < fieldCount; i++)
{
#region 表头及样式
headerRow.CreateCell(i).SetCellValue(fieldStringArray[i]);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.AutoSizeColumn(i);
#endregion
}
var count = list.Count();
#region 单元格样式
ICellStyle styleCell = workbook.CreateCellStyle();
styleCell.Alignment = HorizontalAlignment.Center; //居中
styleCell.VerticalAlignment = VerticalAlignment.Center; //垂直居中
#endregion
for (int i = 0; i < count; i++)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(i + 1);
var data = list[i];
for (int cellIndex = 0; cellIndex < fieldCount; cellIndex++)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(cellIndex, CellType.String);
var property = propertiesUsed[cellIndex];
if (Attribute.IsDefined(property, typeof(TimeAttribute)))
{
try
{
TimeSpan ts = new TimeSpan(0, 0, (int)property.GetValue(data));
StringBuilder sb = new StringBuilder();
if ((int)ts.TotalHours > 0)
{
sb.Append((int)ts.TotalHours + "h");
}
if (ts.Minutes > 0)
{
sb.Append(ts.Minutes + "m");
}
if (ts.Seconds > 0)
{
sb.Append(ts.Seconds + "s");
}
newCell.SetCellValue(sb.ToString());
}
catch (Exception ex)
{
ILogger logger = ServiceProviderServiceExtensions.GetRequiredService <ILogger>(
ServiceProviderExtension.ServiceProvider);
logger.LogError($"Second转换失败：" + ex.Source + Environment.NewLine + ex.StackTrace + Environment.NewLine + ex.Message + Environment.NewLine + ex.InnerException);
newCell.SetCellValue(property.GetValue(data).ToString());
}
}
else
{
var propertyValue = property.GetValue(data);
if (propertyValue == null)
{
newCell.SetCellValue("");
}
else
{
newCell.SetCellValue(propertyValue.ToString());
}
}
newCell.CellStyle = styleCell;
}
}
//统一设置列宽度
sheet.SetColumnWidth(fieldCount);
}
