去除重复的Arraylist数据
public static ArrayList DedupStringList(ArrayList list)
{
if (list == null)
{
return new ArrayList(0);
}
ArrayList result = new ArrayList();
Dictionary<string, string> hashset = new Dictionary<string, string>();
foreach (string str in list)
{
if (string.IsNullOrEmpty(str.Trim()))
{
continue;
}
string s = str.Trim().ToLower();
if (!hashset.ContainsKey(s))
{
hashset.Add(s, string.Empty);
result.Add(s);
}
}
result.TrimToSize();
return result;
}
调用
ArrayList strYear = new ArrayList();
ArrayList AL = new ArrayList();
YEAR = RQ.Substring(0, 4);
strYear.Add(YEAR);
private void btn_PRINT_Click(object sender, EventArgs e)
{
if (XSGZ_SPD.ActiveSheet.RowCount == 0)
{
MessageBox.Show("没有数据");
return;
}
DialogResult result = MessageBox.Show("是否要生成 EXCEL ?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
AL = DedupStringList(strYear);
Excel.Application excel = new Excel.Application();
string xlsming = "";
xlsming = "BXMX_BB070a.xls";
Excel.Workbook workbook = excel.Workbooks.Open(Application.StartupPath + "//Model//" + xlsming,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
int count = 0;
for (int t=0; t < AL.Count;t++ )
{
string aa = AL[t].ToString();
worksheet = (Excel.Worksheet)workbook.Sheets[1];
if (XSGZ_SPD.ActiveSheet.GetText(0, 18).ToString() == "上限")
{
worksheet.Cells[1, 1] = aa + "年度职工缴" + XSGZ_SPD.ActiveSheet.GetText(0, 17).ToString() + " 超上限工资额汇总表";
}
else if (XSGZ_SPD.ActiveSheet.GetText(0, 18).ToString() == "下限")
{
worksheet.Cells[1, 1] = aa + "年度职工缴" + XSGZ_SPD.ActiveSheet.GetText(0, 17).ToString() + " 不足下限工资额汇总表";
}
worksheet = (Excel.Worksheet)workbook.Sheets[1];
count++;
worksheet.Copy(Missing.Value, workbook.Sheets.get_Item(count));
worksheet = (Excel.Worksheet)workbook.Sheets[count + 1];
worksheet.Name = aa;
int j = 0;
double sumYi = 0.0;
double sumER = 0.0;
double March = 0.0;
double April = 0.0;
double May = 0.0;
double June = 0.0;
double July = 0.0;
double August = 0.0;
double September = 0.0;
double October = 0.0;
double November = 0.0;
double December = 0.0;
double sums = 0.0;
int x = 4;
for (int i = 0; i < XSGZ_SPD.ActiveSheet.RowCount; i++)
{
if (aa == XSGZ_SPD.ActiveSheet.GetText(i, 1).ToString())
{
worksheet.get_Range("a" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 0).ToString();
worksheet.get_Range("b" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 2).ToString();
worksheet.get_Range("c" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 3).ToString();
worksheet.get_Range("d" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 4).ToString();
worksheet.get_Range("e" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 5).ToString();
worksheet.get_Range("f" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 6).ToString();
worksheet.get_Range("g" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 7).ToString();
worksheet.get_Range("h" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 8).ToString();
worksheet.get_Range("i" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 9).ToString();
worksheet.get_Range("J" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 10).ToString();
worksheet.get_Range("k" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 11).ToString();
worksheet.get_Range("l" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 12).ToString();
worksheet.get_Range("m" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 13).ToString();
worksheet.get_Range("n" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 14).ToString();
worksheet.get_Range("o" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 15).ToString();
worksheet.get_Range("p" + Convert.ToString(j + 4), Missing.Value).Value2 = XSGZ_SPD.ActiveSheet.GetText(i, 16).ToString();
if (XSGZ_SPD.ActiveSheet.GetText(i, 4)!="")
{
sumYi += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 4).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 5) != "")
{
sumER += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 5).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 6) != "")
{
March += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 6).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 7) != "")
{
April += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 7).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 8) != "")
{
May += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 8).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 9) != "")
{
June += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 9).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 10) != "")
{
July += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 10).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 11) != "")
{
August += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 11).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 12) != "")
{
September += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 12).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 13) != "")
{
October += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 13).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 14) != "")
{
November += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 14).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 15) != "")
{
December += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 15).ToString());
}
if (XSGZ_SPD.ActiveSheet.GetText(i, 16) != "")
{
sums += Convert.ToDouble(XSGZ_SPD.ActiveSheet.GetText(i, 16).ToString());
}
j++;
worksheet.get_Range("A" + x, "P" + x).Borders.LineStyle = 1;
x++;
}
}
worksheet.get_Range("a" + Convert.ToString(j + 4), "c" + Convert.ToString(j + 4)).Merge(Missing.Value);
worksheet.get_Range("a" + Convert.ToString(j + 4), "c" + Convert.ToString(j + 4)).Value2 = "合计";
worksheet.get_Range("a" + Convert.ToString(j + 4), "c" + Convert.ToString(j + 4)).HorizontalAlignment =Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range("d" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(sumYi);
worksheet.get_Range("e" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(sumER);
worksheet.get_Range("f" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(March);
worksheet.get_Range("g" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(April);
worksheet.get_Range("h" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(May);
worksheet.get_Range("i" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(June);
worksheet.get_Range("j" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(July);
worksheet.get_Range("k" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(August);
worksheet.get_Range("l" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(September);
worksheet.get_Range("m" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(October);
worksheet.get_Range("n" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(November);
worksheet.get_Range("o" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(December);
worksheet.get_Range("p" + Convert.ToString(j + 4), Missing.Value).Value2 = Convert.ToString(sums);
worksheet.get_Range("A" + x, "P" + x).Borders.LineStyle = 1;
}
try
{
System.GC.Collect();
worksheet = (Excel.Worksheet)workbook.Sheets[1];
worksheet.Delete();
excel.Visible = true;
string strExcelFileName = "汇总表";
DirectoryInfo di3 = Directory.CreateDirectory(@"D://工资系统报表//工资报表//汇总表");
if (di3.Exists == false)
{
di3.Create();
}
DateTime dt = DateTime.Now;
string shijian = DateTime.Now.Hour.ToString().PadLeft(2, '0') +
DateTime.Now.Minute.ToString().PadLeft(2, '0') +
DateTime.Now.Second.ToString().PadLeft(2, '0');
strExcelFileName = strExcelFileName + dt.ToString("yyyyMMdd") + shijian;
excel.Visible = true;
excel.ActiveWorkbook.SaveAs("D://工资系统报表//工资报表//汇总表" + "//" + strExcelFileName + " " + txt_JS_YEAR.Text + ".XLS", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ep)
{
excel.Quit();
excel = null;
MessageBox.Show(ep.ToString(), "提示", MessageBoxButtons.OK);
}
}
}